From: Peter Brawley Date: February 24 2006 5:39am Subject: Re: Query returns to many results List-Archive: http://lists.mysql.com/mysql/195139 Message-Id: <43FE9C0C.8070201@earthlink.net> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="=======AVGMAIL-43FE9C0C0880=======" --=======AVGMAIL-43FE9C0C0880======= Content-Type: multipart/alternative; boundary=------------040001000105020209090404 --------------040001000105020209090404 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit />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 () 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! > --------------040001000105020209090404 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit >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!

--------------040001000105020209090404-- --=======AVGMAIL-43FE9C0C0880======= Content-Type: text/plain; x-avg=cert; charset=us-ascii Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Content-Description: "AVG certification" 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 --=======AVGMAIL-43FE9C0C0880=======--