#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2011
    Posts
    6
    Rep 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.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,370
    Rep Power
    391
    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.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2011
    Posts
    6
    Rep Power
    0
    Thanks for the reply swampBoogie. I understand this:

    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:

    Originally Posted by swampBoogie
    Move those restrictions to an apropriate on clause instead.
    Could you elaborate on this?

    Thank you.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,027
    Rep Power
    4210
    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
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2011
    Posts
    6
    Rep 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.

IMN logo majestic logo threadwatch logo seochat tools logo