Complex MySQL Query
Moderator: General Moderators
Complex MySQL Query
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
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
-
magicrobotmonkey
- Forum Regular
- Posts: 888
- Joined: Sun Mar 21, 2004 1:09 pm
- Location: Cambridge, MA
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;-
magicrobotmonkey
- Forum Regular
- Posts: 888
- Joined: Sun Mar 21, 2004 1:09 pm
- Location: Cambridge, MA
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;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...
and the members
There are obviosly more fields, but they aren't important.
Thanks for your help,
Josh
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'Code: Select all
ID = Primary KeyThanks for your help,
Josh
-
magicrobotmonkey
- Forum Regular
- Posts: 888
- Joined: Sun Mar 21, 2004 1:09 pm
- Location: Cambridge, MA