Solved

ASP MSSQL - inner Join 3 tables and union another

Posted on 2007-11-20
7
2,502 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

810 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