Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

ASP MSSQL - inner Join 3 tables and union another

Posted on 2007-11-20
7
Medium Priority
?
2,510 Views
Last Modified: 2010-04-21
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
Comment
Question by:sparky74
7 Comments
 
LVL 8

Expert Comment

by:digital_thoughts
ID: 20319846
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
 
LVL 7

Expert Comment

by:multithreading
ID: 20319862
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
 
LVL 18

Expert Comment

by:JR2003
ID: 20319867
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:sparky74
ID: 20320023
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
 

Author Comment

by:sparky74
ID: 20320067
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
 
LVL 8

Accepted Solution

by:
digital_thoughts earned 2000 total points
ID: 20320710
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
 

Author Closing Comment

by:sparky74
ID: 31410117
very well set out code - thanks
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question