Vincente Aggrippino <[EMAIL PROTECTED]> wrote on 01/12/2006 12:47:31
AM:
> 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
Yes, your statement is still unstable.
Because the comma operator now has a lower precedence than an explicit
JOIN command if you wanted to left join to anything other than `delivery`
you would need to use parentheses to make sure your JOINs were occurring
in the correct sequence. As an example, if you had added something like
this to your query:
LEFT JOIN other_table ot
on ot.company_id = co.id
You would more than likely get an error because at the time of the parsing
of that third JOIN, the table `company` will not have been added to the
hierarchy of tables participating in the query. You would need to add a
set of parentheses to explicitly change the order of operations so that
`company` becomes part of the query earlier than the comma command
normally allows it to be. If all of your JOINs were explicit, that problem
goes away because all of your <table ref> operators will have the same
priority (precedence).
Also, I do no know for a fact that the "on" conditional of an implicit
JOIN are evaluated before the WHERE clause (at the same time the other
explicit ON clauses are being evaluated) or if they are delayed to be
evaluated with the other conditions in the WHERE clause. Delaying that
evaluation could mean a significant performance hit if you were implicitly
joining to just a few records in a rather large table.
Try it both ways and see if one is faster than the other.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
