"left join" in one table and not in another 1

[Ghodmode]

I have three tables joined on key fields: [tt]delivery[/tt] is joined with [tt]invoice_detail[/tt] on [tt]delivery_id[/tt] and with [tt]location[/tt] on [tt]loc_id[/tt]. 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 [tt]d.loc_id in ( select ... from location ...[/tt], 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.

--
-- Ghodmode

 

[TonyGroves]

Your query uses the comma inner join operator as well as a left join. In this case, it's not clear which join is done first. You need to rewrite your query to replace the comma join with INNER JOIN ... ON, and use brackets if the intended join sequence is not linear.

 

[Ghodmode]

Thank you. I'm getting closer to understanding. I hadn't thought of the comma as a join operator itself, but I guess that's exactly what it is.

I received a solution from the MySQL general mailing list which worked for me. All I needed to do was separate the two left joined tables. I still don't understand exactly why it works, though.

With regard to the precedence, if I do an inner join between company and delivery and I do a left join with the other two tables, isn't it the same regardless of whether I do the inner join before or after the left joins? The results of the left joins don't reduce the records from the delivery table because the delivery table is always on the left.

This is the query I ended up using, but it isn't exactly what the person who provided it recommended. They make the same point that you do: mixing an implicit cross/inner join and an explicit left join causes problems. I did it like this because it seems cleaner to me and it works ...

select
    d.co_id,
    co.name,
    count(*)
from
    company co,
    delivery d
    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.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

I started learning SQL on Oracle and I never used the inner, left, outer, etc. syntax. I just used (+) after the field that could be null in the result set. I thought of it as an optional field.

--
-- Ghodmode