Vincente Aggrippino <[EMAIL PROTECTED]> wrote on 01/11/2006 11:33:38
PM:
> 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.
First problem: you are mixing implicit CROSS JOINS and explicit LEFT
JOINS. As of 5.0.12 that is a recipe for trouble. Here is a repaired
version:
select
d.co_id,
co.name,
count(*)
from company co
INNER JOIN delivery d
ON d.co_id = co.co_id
left join invoice_detail id
ON d.delivery_id = id.delivery_id
LEFT JOIN location loc
ON d.loc_id = loc.loc_id
where 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
That may have just fixed it... Try it out and let us know.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
