conditional outer join

[invantix]

Orders
OrderNumber
Description
...

Invoice
OrderNumber
Status
...

I want all orders for which there is no invoice record OR if there is an invoice record the status != 'C'

Thanks,

Chris

 

[r937]

select OrderNumber
     , Description
  from Orders
left outer
  join Invoice
    on Orders.OrderNumber
     = Invoice.OrderNumber
   and Invoice.Status = 'C'
 where Invoice.OrderNumber is null

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[dnoeth]

Hi Rudy,
your query doesn't work if there's an order with both status = C and status <> 'C'.

A slight modification fixes it:

select distinct
rderNumber
, o.Description
from orders o
left outer
join Invoice i
on rderNumber
= i.OrderNumber
where i.OrderNumber is null
or i.status <> 'c'


I couldn't find a single Exists query :-(

select *
from orders o
where not exists
(select * from invoice i
where rderNumber = i.OrderNumber
)
or
exists
(select * from invoice i
where rderNumber = i.OrderNumber
and i.Status <> 'C'
)


Dieter