Page 1 of 1

Complex MySQL Query

Posted: Sun Jan 30, 2005 6:09 pm
by jwalsh
Hi,

I have two tables, one called members, and one called article.

Each article has a memberID that references a member in the database. Simple enough.

What I want to do is create a function that will return an array with all members with 10 or more articles. I'm assuming this is going to take some recursive looping, but I'm a bit confused how to do it.

Surely this isn't all that uncommon, what's the best way to go about it?

Thanks,

Josh

Posted: Sun Jan 30, 2005 6:19 pm
by magicrobotmonkey
Something like

Code: Select all

SELECT COUNT(ArticleID) NumArts, MemberName,  MemberID FROM Members, Articles WHERE Articles.MemberID = Members.MemberID AND NumArts > 10 GROUP BY Members.MemberID;

Posted: Sun Jan 30, 2005 6:26 pm
by jwalsh
Whoa... Complex. :) This query is a bit complex, can you break it down a bit so I can comprehend how it works...

Posted: Sun Jan 30, 2005 6:33 pm
by magicrobotmonkey
sure

Code: Select all

the first bit COUNT(ArticleID) does just that, it adds 1 for each articleID it finds. We then ALIAS that (With or wothout the AS) to NumArts so now NumArts is equal to the count of ArticleIDs (aka the count of articles)

SELECT COUNT(ArticleID) AS NumArts, MemberName,  MemberID 

FROM Members, Articles 

here the two tables are linked together and we use the alias for the total number of articles to filter out only those members with a certain number of articles.
WHERE Articles.MemberID = Members.MemberID AND NumArts > 10 

this is the part that makes the counting work it only returns one row for each memberID. So each member will have one record record AND all the articles for that member get kind of bunched together which is what makes the COUNTt work
GROUP BY Members.MemberID;

Posted: Sun Jan 30, 2005 6:46 pm
by jwalsh
Ok, now I see why I'm confused, I made a slight mistake in what I was asking.

I need the query to return a list of articles for each member that has 10 or more articles, not a list of members... Should be able to just swap a few of those vars around to get it to work.

Thanks,

Josh

Posted: Sun Jan 30, 2005 7:14 pm
by jwalsh
Ok, Now that I've throughly confused myself, and probably half of this forums readers... let me start over, and break this down a little better.

The idea is that articles by someone who already has 10+ articles approved by us will be more likely to have better content, and should be approved first. So I'm making a seperate section that shows all pending articles from members with 10+ approved articles.

Here's the structure of the article table...

Code: Select all

ID = Primary Key
MemberID = ID of author in members table
Title = Article Title
Status = 'pending' or 'active'
and the members

Code: Select all

ID = Primary Key
There are obviosly more fields, but they aren't important.

Thanks for your help,

Josh

Posted: Sun Jan 30, 2005 7:57 pm
by magicrobotmonkey
i think you might need two separate queries for that. First the one above to get members with 10+ articles and second on to select all pending articles for that member.

Posted: Sun Jan 30, 2005 8:58 pm
by jwalsh
Great thanks :)