Outer Joins on Composite Keys using Join Syntax in the From Clause (ANSI SQL:1992)

• tibins

  SSC Enthusiast

  Points: 105

  More actions

  September 11, 2007 at 8:26 pm

  #180826

  Can anyone tell me how to rewrite this query in ANSI SQL: 1992 instead of using the *= operator for the outer join? I've got two tables, both with composite keys and need to join them with an inner join on one column and an outer join on the other. I know I can run the query below if I change my compatibility level to 80 but don't want to do that.

  select ccp.part_id, ccp.warehouse_id, ccp.count_freq,

  ccp.last_count_date,pl.qty

  from cycle_count_part ccp, part_location pl

  where ccp.part_id = pl.part_id

  and ccp.warehouse_id *= pl.warehouse_id and

  ccp.warehouse_id in ('GSS_PROD','AIRCRAFT_PROD')

  Many thanks!

• Greg Walker

  Old Hand

  Points: 369

  More actions

  September 12, 2007 at 5:30 am

  #733059

  select

  ccp.part_id,

  ccp.warehouse_id,

  ccp.count_freq,

  ccp.last_count_date,

  pl.qty

  from

  cycle_count_part ccp LEFT JOIN part_location pl ON ccp.part_id = pl.part_id AND ccp.warehouse_id = pl.warehouse_id

  where pl.part_id IS NOT NULL

  AND

  ccp.warehouse_id in ('GSS_PROD','AIRCRAFT_PROD')

   

  ...sorry for the bad formatting.

  ---

  Greg Walker
  DBA, ExpenseWatch.com