Solved

ASP MSSQL - inner Join 3 tables and union another

Posted on 2007-11-20
7
2,505 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 500 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

Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.

756 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