List:General Discussion« Previous MessageNext Message »
From:SGreen Date:February 23 2006 3:13pm
Subject:Re: Query returns to many results
View as plain text  
Schalk <schalk@stripped> wrote on 02/23/2006 08:55:01 AM:

> George Law wrote:
> > Schalk ,
> >
> > You need to specify the unifying column between your ablb and abm 
tables.
> >
> > ie - in your where, "and ablb.id=abm.id"
> >
> > Once you get this so it returns expected results, you can run the 
> > query, prefaced with
> > "explain" and it will give you an idea on the way mysql is running the 

> > query.  This has helped me determine
> > some additional indexes that greatly speed up my queries.
> >
> > -- 
> > George
> >
> >
> > ----- Original Message ----- From: "Schalk" <schalk@stripped>
> > To: <mysql@stripped>
> > Sent: Thursday, February 23, 2006 6:59 AM
> > Subject: Query returns to many results
> >
> >
> >> Greetings All,
> >>
> >> Please have a look at the following query:
> >>
> >> SELECT abm.mem_number, abm.first_name, abm.last_name, 
> >> abm.area_represented, abm.age, abm.sex, abm.cup,
> >> ablb.*, ablb.jp + ablb.rc + ablb.fsmgp + ablb.gmc + ablb.saly + 
> >> ablb.nwgp + ablb.ecgp + ablb.sams + ablb.wcc + ablb.kzngp + ablb.emc 
> >> + ablb.lmgp + ablb.saff + ablb.gmgp + ablb.safy + ablb.mmw + ablb.lc 
> >> + ablb.mmc + ablb.nwmc + ablb.ncc + ablb.samp + ablb.gsc + ablb.wcmgp 

> >> + ablb.sapm + ablb.kznc + ablb.npc + ablb.smc + ablb.ecc + ablb.mgp + 

> >> ablb.samo + ablb.cofc + ablb.cs + ablb.ncmgp + ablb.fsc + ablb.ggp + 
> >> ablb.tmc + ablb.gc + ablb.yotm AS total_points FROM ab_leader_board 
> >> ablb, ab_members abm
> >> WHERE abm.sex = 'Female' AND abm.cup = 'kids'
> >> ORDER BY total_points DESC
> >>
> >> Now this query is run over two tables and the ab_members table 
> >> contains around 302 rows. Around 1/3 of these will be where cup=kids. 

> >> However, when this query is run it returns 20,700 results :0 Any idea 

> >> why this is? Also, any help or pointers as to how I can optimize this 

> >> query will be much appreciated. Thank you!
> >
> Thanks George! It works perfectly. Now to optimize this bugger.
> 
> -- 
> Kind Regards
> Schalk Neethling
> Web Developer.Designer.Programmer.President
> Volume4.Business.Solution.Developers
> 

Schalk,

You wouldn't have even run into this as an issue if you had used the 
explicit JOIN form. Again, I blame the documentation for only 
demonstrating the "lazy" form of INNER JOIN declaration almost 
exclusively. I believe that by only demonstrating the comma-separated 
join, they have created the impression that it is a "preferred" method. I 
strongly discourage the use of that form of declaring table joins for the 
very reason you posted. If you had used the explicit form:

SELECT 
...(all of your columns)...
FROM ab_leader_board ablb
INNER JOIN ab_members abm
        ON ablb.id=abm.id (or whatever is appropriate)
WHERE ...

It should have be intuitively obvious that you had left out the ON clause 
from your original query. As it was, your missing JOIN conditions were 
just not noticed because of all of the other activity in your whole 
statement. This is a very frequent problem with the join syntax you used 
in your original query.

Again, I implore all SQL coders to use the explicit JOIN syntax on all 
platforms that support it (Oracle being a well-known exception). It makes 
it much easier to catch logical errors just like Schalk ran into in his 
original post. The explicit form is also the only way to declare outer 
joins in MySQL so you will have to use it sooner or later. Please, again, 
I ask the documentation team to modify the SQL examples in the manual 
(especially in the tutorial section) to use the explicit JOIN forms.

Humbly yours,

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine


Thread
Query returns to many resultsSchalk23 Feb
  • Re: Query returns to many resultsGeorge Law23 Feb
    • Re: Query returns to many resultsSchalk23 Feb
      • Re: Query returns to many resultsSGreen23 Feb
        • Re: Query returns to many resultsMartijn Tonies23 Feb
  • Re: Query returns to many resultsPeter Brawley24 Feb
    • Re: Query returns to many resultsSchalk24 Feb