Solved

ASP MSSQL - inner Join 3 tables and union another

Posted on 2007-11-20
7
2,494 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:sparky74
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
very well set out code - thanks
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

762 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now