List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:February 24 2006 5:39am
Subject:Re: Query returns to many results
View as plain text  
/>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 /

That's because your ...

FROM ab_leader_board ablb, ab_members abm

calls for a cross join--it asks for every logically possible combination 
of ablb and abm rows. From the rest of your query, it appears you need 
something like ...

FROM ab_leader_board ablb
INNER JOIN ab_members abm USING (<name_of_joining_column>)

Also, do you really mean to sum all those ablb column values after 
having already called for all ablb column values with ablb.*  ?

PB

-----

Schalk wrote:
> 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!
>

Attachment: [text/html]
No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 268.0.0/267 - Release Date: 2/22/2006
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