homepage Welcome to WebmasterWorld Guest from 23.20.28.193
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Visit PubCon.com
Home / Forums Index / WebmasterWorld / Webmaster General
Forum Library, Charter, Moderators: phranque & physics

Webmaster General Forum

    
MYSQL - Struggling with query
humpo




msg:395110
 3:19 pm on Aug 12, 2005 (gmt 0)

Hereís my problem , hopefully simplified. Have two tables.
Table one contains a list of music genres(only my table has 3,000 types)
Table two contains a lift of albums.
I only want to select a genre that has 5 or more albums.

My attempt so far has been to break the problem into simpler parts, iterating through the Genre table, then performing another query to see if the number of albums for each Genre is greater than 5. As you can imagine this is painfully slow. Iíve had few attempts but only succeeded in locking up my machine :).

If someone could help with problem it would be appreciated.
Many thanks

 

ChadSEO




msg:395111
 3:36 pm on Aug 12, 2005 (gmt 0)

humpo,

I depends on how your tables are setup. Does the album table store the genre? If so, then you could do something like this:

SELECT genre, count(*) as cnt
FROM albums
GROUP BY genre
HAVING count(*) >= 5;

Chad

humpo




msg:395112
 4:36 pm on Aug 12, 2005 (gmt 0)

Thanks Chad.

Thatís worked great. Thought there might a nice solution, iíve never used GROUP BY and HAVING before :)

cheers

ChadSEO




msg:395113
 6:05 pm on Aug 12, 2005 (gmt 0)

You're welcome.

For anyone else reading this thread, HAVING essentially lets you do a WHERE clause after the GROUP BY has aggregated your data. So you can limit queries by min(), max(), count(), etc., which WHERE cannot do.

Glad to hear it worked, humpo. :)

Chad

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / WebmasterWorld / Webmaster General
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved