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.