1 union query vs 3 other queries

Dev Shed Forums Sponsor:

June 18th, 2012, 08:22 AM

paulpitchford

Registered User

Join Date: Aug 2011

Posts: 6

Time spent in forums: 2 h 28 m 12 sec
Reputation Power: 0

1 union query vs 3 other queries

Hi, I have a union query that pulls in all our order numbers from sales and orders. When we place an order with a supplier the reference we us is the order number that the customer placed with us.

We then have 3 other queries which detail deliveries, warehouse issues and factory returns:

They are all like this:
Order Number
Qty Issued

The union table just lists every single order number in the system.

What I would like to do is display all three queries in one query. I presume we need to use the union query in some way but I can't make it show all the records from all of the queries.

This is the SQL I have so far:

Code:

SELECT tst_Paul_UnionJoinOrdersDels.OrderNo, tst_Paul_FilmIssued.[SumOfQty Issued], tst_Paul_FilmDeliveries.SumOfDeldQtyKilos, tst_Paul_FilmReturns.[SumOfQty Issued]
FROM ((tst_Paul_UnionJoinOrdersDels LEFT JOIN tst_Paul_FilmIssued ON tst_Paul_UnionJoinOrdersDels.OrderNo = tst_Paul_FilmIssued.[Order No From]) LEFT JOIN tst_Paul_FilmDeliveries ON tst_Paul_UnionJoinOrdersDels.OrderNo = tst_Paul_FilmDeliveries.OrderNo) LEFT JOIN tst_Paul_FilmReturns ON tst_Paul_UnionJoinOrdersDels.OrderNo = tst_Paul_FilmReturns.[Order No From]
WHERE (((tst_Paul_FilmIssued.[SumOfQty Issued])>=0)) OR (((tst_Paul_FilmDeliveries.SumOfDeldQtyKilos)>=0)) OR (((tst_Paul_FilmReturns.[SumOfQty Issued])>=0));

This picks all the deliveries and returns up fine but for some reason it's not picking up all of the issues.

Could someone please point me in the right direction as I'm just going in circles at the moment and this is about as close as I can get it. Please feel free to ask for more information if this is not clear enough.

Thank you,

Paul.

June 18th, 2012, 08:56 AM

swampBoogie

Contributing User

Join Date: Jan 2003

Location: Paris Uppland

Posts: 2,349

Time spent in forums: 1 Month 4 Days 7 h 17 m 46 sec
Reputation Power: 390

As you have restrictions on the right hand table of a left join in the where clause, the query will behave as an inner join. Move those restrictions to an apropriate on clause instead.

June 18th, 2012, 09:03 AM

paulpitchford

Registered User

Join Date: Aug 2011

Posts: 6

Time spent in forums: 2 h 28 m 12 sec
Reputation Power: 0

Thanks for the reply swampBoogie. I understand this:

Quote:

Originally Posted by swampBoogie

As you have restrictions on the right hand table of a left join in the where clause, the query will behave as an inner join.

But unfortunately I don't understand this:

Quote:

Originally Posted by swampBoogie

Move those restrictions to an apropriate on clause instead.

Could you elaborate on this?

Thank you.

June 18th, 2012, 11:09 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,369

Time spent in forums: 3 Months 1 Week 2 Days 6 h 14 m 57 sec
Reputation Power: 4140

Quote:

Originally Posted by paulpitchford

Could you elaborate on this?

it's a true pity that microsoft access does such a shïtty job of formatting sql, not only running everything into one long continuous line, but tossing (all those (useless (ridiculous))) parentheses all over da place

let's first just simply reformat your query --

Code:

SELECT tst_Paul_UnionJoinOrdersDels.OrderNo
     , tst_Paul_FilmIssued.[SumOfQty Issued]
     , tst_Paul_FilmDeliveries.SumOfDeldQtyKilos
     , tst_Paul_FilmReturns.[SumOfQty Issued]
  FROM ((
       tst_Paul_UnionJoinOrdersDels 
LEFT 
  JOIN tst_Paul_FilmIssued 
    ON tst_Paul_UnionJoinOrdersDels.OrderNo = tst_Paul_FilmIssued.[Order No From]
       ) 
LEFT 
  JOIN tst_Paul_FilmDeliveries 
    ON tst_Paul_UnionJoinOrdersDels.OrderNo = tst_Paul_FilmDeliveries.OrderNo
       ) 
LEFT 
  JOIN tst_Paul_FilmReturns 
    ON tst_Paul_UnionJoinOrdersDels.OrderNo = tst_Paul_FilmReturns.[Order No From]
 WHERE (((tst_Paul_FilmIssued.[SumOfQty Issued])>=0)) 
    OR (((tst_Paul_FilmDeliveries.SumOfDeldQtyKilos)>=0)) 
    OR (((tst_Paul_FilmReturns.[SumOfQty Issued])>=0));

now it's easier to see the conditions after they've been moved to the ON clauses --

Code:

SELECT tst_Paul_UnionJoinOrdersDels.OrderNo
     , tst_Paul_FilmIssued.[SumOfQty Issued]
     , tst_Paul_FilmDeliveries.SumOfDeldQtyKilos
     , tst_Paul_FilmReturns.[SumOfQty Issued]
  FROM ((
       tst_Paul_UnionJoinOrdersDels 
LEFT 
  JOIN tst_Paul_FilmIssued 
    ON tst_Paul_UnionJoinOrdersDels.OrderNo = tst_Paul_FilmIssued.[Order No From]
   AND (((tst_Paul_FilmIssued.[SumOfQty Issued])>=0)) 
       ) 
LEFT 
  JOIN tst_Paul_FilmDeliveries 
    ON tst_Paul_UnionJoinOrdersDels.OrderNo = tst_Paul_FilmDeliveries.OrderNo
   AND (((tst_Paul_FilmDeliveries.SumOfDeldQtyKilos)>=0)) 
       ) 
LEFT 
  JOIN tst_Paul_FilmReturns 
    ON tst_Paul_UnionJoinOrdersDels.OrderNo = tst_Paul_FilmReturns.[Order No From]
   AND (((tst_Paul_FilmReturns.[SumOfQty Issued])>=0))

sadly, access is gonna bitch about the AND in the ON clauses ("join expression not supported" or similar), so here we are forced to add more parentheses

as a last step, i like to use table aliases ( thus reducing the total number of characters of code, making the forest easier to see amongst all the trees), as well as removing unnecessary parentheses and switching the join columns in the ON clause

Code:

SELECT o.OrderNo
     , i.[SumOfQty Issued]
     , d.SumOfDeldQtyKilos
     , r.[SumOfQty Issued]
  FROM ((
       tst_Paul_UnionJoinOrdersDels AS o
LEFT 
  JOIN tst_Paul_FilmIssued AS i
    ON ( i.[Order No From] = o.OrderNo 
     AND i.[SumOfQty Issued] >= 0 ) 
       ) 
LEFT 
  JOIN tst_Paul_FilmDeliveries AS d
    ON ( d.OrderNo = o.OrderNo
     AND d.SumOfDeldQtyKilos >=0 ) 
       ) 
LEFT 
  JOIN tst_Paul_FilmReturns AS r
    ON ( r.[Order No From] = o.OrderNo 
     AND r.[SumOfQty Issued] >=0 )

helps?

__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book

June 20th, 2012, 04:40 AM

paulpitchford

Registered User

Join Date: Aug 2011

Posts: 6

Time spent in forums: 2 h 28 m 12 sec
Reputation Power: 0

Hi,

Thanks for the detailed response. As it happens that didn't resolve my problem but I learnt enough from your post to figure it myself. Looking back maybe I didn't explain the question well enough.

Again thank you for such a concise response though. It really helped me.

Paul.

1 union query vs 3 other queries

Developer Shed Advertisers and Affiliates