• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2515
  • Last Modified:

ASP MSSQL - inner Join 3 tables and union another

Hi

I have the following code which pulls my order details from the orders table, joins my labelref & consignment tables.

SELECT orders.orderid AS myorder, delivery_name, Delivery_Address2, Delivery_Address1,Delivery_City, Delivery_Region, delivery_postcode, citylinkinstructions, despatchdate,  labelref, labelqty, consignment, manifest, citylinkservice, barcode
FROM Orders INNER JOIN labelref ON orders.orderid = labelref.orderid INNER JOIN consignment ON orders.orderid = consignment.orderid
WHERE despatchdate = '11/20/2007' and cardvaild ='y' and labelref.void='1' and consignment.void='1' and manifest ='1'
ORDER BY MYORDER, labelref ASC

I now need to get the same information from the 'savedorders' table as well as the 'orders' table. I'm thinking this will use union all and then the inner joins, but I am confused.

any help appreciated


0
sparky74
Asked:
sparky74
1 Solution
 
digital_thoughtsCommented:
You can do what you need a few ways, here's one, which is just two queries, unioned:

SELECT
      orders.orderid AS myorder,
      delivery_name,
      Delivery_Address2,
      Delivery_Address1,
      Delivery_City,
      Delivery_Region,
      delivery_postcode,
      citylinkinstructions,
      despatchdate,  
      labelref,
      labelqty,
      consignment,
      manifest,
      citylinkservice,
      barcode
FROM
      Orders INNER JOIN labelref ON orders.orderid = labelref.orderid
      INNER JOIN consignment ON orders.orderid = consignment.orderid
WHERE
      despatchdate = '11/20/2007'
      and cardvaild ='y'
      and labelref.void='1'
      and consignment.void='1'
      and manifest ='1'
UNION
SELECT
      orders.orderid AS myorder,
      delivery_name,
      Delivery_Address2,
      Delivery_Address1,
      Delivery_City,
      Delivery_Region,
      delivery_postcode,
      citylinkinstructions,
      despatchdate,  
      labelref,
      labelqty,
      consignment,
      manifest,
      citylinkservice,
      barcode
FROM
      SavedOrders INNER JOIN labelref ON orders.orderid = labelref.orderid
      INNER JOIN consignment ON orders.orderid = consignment.orderid
WHERE
      despatchdate = '11/20/2007'
      and cardvaild ='y'
      and labelref.void='1'
      and consignment.void='1'
      and manifest ='1'
ORDER BY
      MYORDER,
      labelref ASC

The other is more like what you mentioned, with the union first, then the inner joins:

SELECT
      orders.orderid AS myorder,
      delivery_name,
      Delivery_Address2,
      Delivery_Address1,
      Delivery_City,
      Delivery_Region,
      delivery_postcode,
      citylinkinstructions,
      despatchdate,  
      labelref,
      labelqty,
      consignment,
      manifest,
      citylinkservice,
      barcode
FROM
      (
            SELECT
                  *
            FROM
                  Orders
            UNION
            SELECT
                  *
            FROM
                  SavedOrders
      ) Orders INNER JOIN labelref ON orders.orderid = labelref.orderid
      INNER JOIN consignment ON orders.orderid = consignment.orderid
WHERE
      despatchdate = '11/20/2007'
      and cardvaild ='y'
      and labelref.void='1'
      and consignment.void='1'
      and manifest ='1'
ORDER BY
      MYORDER,
      labelref ASC
0
 
multithreadingCommented:
SELECT orders.orderid AS myorder, delivery_name, Delivery_Address2, Delivery_Address1,Delivery_City, Delivery_Region, delivery_postcode, citylinkinstructions, despatchdate,  labelref, labelqty, consignment, manifest, citylinkservice, barcode
FROM Orders INNER JOIN labelref ON orders.orderid = labelref.orderid INNER JOIN consignment ON orders.orderid = consignment.orderid
WHERE despatchdate = '11/20/2007' and cardvaild ='y' and labelref.void='1' and consignment.void='1' and manifest ='1'

union all

SELECT savedorders.orderid AS myorder, delivery_name, Delivery_Address2, Delivery_Address1,Delivery_City, Delivery_Region, delivery_postcode, citylinkinstructions, despatchdate,  labelref, labelqty, consignment, manifest, citylinkservice, barcode
FROM Orders INNER JOIN labelref ON savedorders.orderid = labelref.orderid INNER JOIN consignment ON savedorders.orderid = consignment.orderid
WHERE despatchdate = '11/20/2007' and cardvaild ='y' and labelref.void='1' and consignment.void='1' and manifest ='1'

ORDER BY MYORDER, labelref ASC
0
 
JR2003Commented:
I'm not sure which columns come from which tables, you should prefix all the columns with the name of the table. I've assumed only the OrderId comes from the Orders table, if there are more columns just addthem to the inline select clause.
This should do it:

SELECT O.orderid AS myorder, delivery_name, Delivery_Address2, Delivery_Address1,Delivery_City, Delivery_Region, delivery_postcode, citylinkinstructions, despatchdate,  labelref, labelqty, consignment, manifest, citylinkservice, barcode
  FROM (Select orderid FROM Orders UNION Select orderid FROM SavedOrders) AS O
 INNER JOIN labelref
         ON O.orderid = labelref.orderid
 INNER JOIN consignment
         ON O.orderid = consignment.orderid
 WHERE despatchdate = '11/20/2007' and cardvaild ='y' and labelref.void='1' and consignment.void='1' and manifest ='1'
 ORDER BY MYORDER, labelref ASC
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
sparky74Author Commented:
Hi multithreading

I'm getting the following error with your solution,

the column prefix 'savedorders' does not match with a table name or alias named used in the query, this appears 3 times when i test the code in dreamweaver.

any ideas
0
 
sparky74Author Commented:
Hi digital thoughts

I get a similar error in your code

the column prefix 'orders' does not match with a table name or alias named used in the query, this appears 3 times when i test the code in dreamweaver.
0
 
digital_thoughtsCommented:
Sorry, had a syntax error, give this a try:

SELECT
      orders.orderid AS myorder,
      delivery_name,
      Delivery_Address2,
      Delivery_Address1,
      Delivery_City,
      Delivery_Region,
      delivery_postcode,
      citylinkinstructions,
      despatchdate,  
      labelref,
      labelqty,
      consignment,
      manifest,
      citylinkservice,
      barcode
FROM
      Orders INNER JOIN labelref ON orders.orderid = labelref.orderid
      INNER JOIN consignment ON orders.orderid = consignment.orderid
WHERE
      despatchdate = '11/20/2007'
      and cardvaild ='y'
      and labelref.void='1'
      and consignment.void='1'
      and manifest ='1'
UNION ALL
SELECT
      SavedOrders.orderid AS myorder,
      delivery_name,
      Delivery_Address2,
      Delivery_Address1,
      Delivery_City,
      Delivery_Region,
      delivery_postcode,
      citylinkinstructions,
      despatchdate,  
      labelref,
      labelqty,
      consignment,
      manifest,
      citylinkservice,
      barcode
FROM
      SavedOrders INNER JOIN labelref ON SavedOrders.orderid = labelref.orderid
      INNER JOIN consignment ON SavedOrders.orderid = consignment.orderid
WHERE
      despatchdate = '11/20/2007'
      and cardvaild ='y'
      and labelref.void='1'
      and consignment.void='1'
      and manifest ='1'
ORDER BY
      MYORDER,
      labelref ASC
0
 
sparky74Author Commented:
very well set out code - thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now