Complex MySQL Query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
jwalsh
Forum Contributor
Posts: 202
Joined: Sat Jan 03, 2004 4:55 pm
Location: Cleveland, OH

Complex MySQL Query

Post 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
magicrobotmonkey
Forum Regular
Posts: 888
Joined: Sun Mar 21, 2004 1:09 pm
Location: Cambridge, MA

Post 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;
User avatar
jwalsh
Forum Contributor
Posts: 202
Joined: Sat Jan 03, 2004 4:55 pm
Location: Cleveland, OH

Post by jwalsh »

Whoa... Complex. :) This query is a bit complex, can you break it down a bit so I can comprehend how it works...
magicrobotmonkey
Forum Regular
Posts: 888
Joined: Sun Mar 21, 2004 1:09 pm
Location: Cambridge, MA

Post 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;
User avatar
jwalsh
Forum Contributor
Posts: 202
Joined: Sat Jan 03, 2004 4:55 pm
Location: Cleveland, OH

Post 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
User avatar
jwalsh
Forum Contributor
Posts: 202
Joined: Sat Jan 03, 2004 4:55 pm
Location: Cleveland, OH

Post 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
magicrobotmonkey
Forum Regular
Posts: 888
Joined: Sun Mar 21, 2004 1:09 pm
Location: Cambridge, MA

Post 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.
User avatar
jwalsh
Forum Contributor
Posts: 202
Joined: Sat Jan 03, 2004 4:55 pm
Location: Cleveland, OH

Post by jwalsh »

Great thanks :)
Post Reply