On 1/12/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
>
>
>
> 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.
That fixed it... Thank you! But I don't understand how. Isn't my implicit
inner join the same as the explicit one you used?
I read Join Syntax in the ref. manual. Is it related to the new order of
precedence for the JOIN operator?
Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
The other difference with my original one was that I had both join tables in
one left join section. I played with it a little bit. If I separate them
like yours, it still works with the implicit cross/inner join...
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
Does this method also have a potential problem?
Thanks,
Vince
