Newbie trying to solve a MySQL query logic issue

Question

I'm going to try to explain this as simply as possible. Any help would be greatly appreciated as I'm not very good with designing queries.

I have 3 tables: (I have cut down the unnecessary columns in the users table for this question)

USERS:
id,    username,    password

PENDINGREVIEW:
id,    articleid,    requestid,    content,    writerid,    datewritten

ARTICLES:
id,    title,    length,    requestedby,    content,    writtenby,    daterequested,    datecompleted

So say I have review.php. On review.php I want it to show all the articles that are in the pendingreview table for a specific user. So something like this:

SELECT * FROM pendingreview WHERE requestid = $userid (where $userid = the id of the user)

What I actually want to display on review.php though is a list of articles from the articles table and how many articles are in the pendingreview table that correspond to each of those articles. The list must only be made up of articles that the user has requested.

So I want review.php to look like this:

• Your "How to build a bike" article request has 3 articles pending review.
• Your "How to mow the lawn" article request has 12 articles pending review.

Any help on how I would do this would be appreciated!

Answer 1

SELECT  a.title, COUNT(*) TotalPending
FROM    Articles a
        INNER JOIN PendingReview b  
            ON a.ID = b.articleID
WHERE   b.RequestID = 'ID HERE'
GROUP   BY a.title

To further gain more knowledge about joins, kindly visit the link below:

• Visual Representation of SQL Joins