I have three tables joined on key fields: delivery is joined with
invoice_detail on delivery_id and with location on loc_id. I want to return
records of deliveries that have corresponding records in the location table,
but have no corresponding records in the invoice table.
Here's the query I'm attempting to use:
select
d.co_id,
co.name,
count(*)
from
company co,
delivery d
left join ( invoice_detail id, location loc ) on (
d.delivery_id = id.delivery_id
and d.loc_id = loc.loc_id
)
where
d.co_id = co.co_id
and d.unit_price is not null
and unit_charge is not null
and id.delivery_id is null
and loc.loc_id is not null
group by d.co_id, co.name
If I take out either one of the table references in the left join and
criteria, it works fine. For example, I can either return deliveries that
have corresponding locations or don't have corresponding invoice
records. But I can't return records that have locations and don't have
invoice records. I get 0 records in the result set.
I'm sure I can get this to work if I use something like d.loc_id in ( select
... from location ..., but I want to avoid that if possible because I think
that subquery retrieves the entire contents of the table for comparison.
Does anyone have any idea why my query isn't working?
Any help or ideas are greatly appreciated.
