SQL with subqueries

Question

I remember doing this in DB class in what feels like ages ago, but can't recall it and can't seem to search for the right words.

I am trying to essentially turn a query that produces a foreach and then runs another query based on information from the first one, into one query. More clearly, I have two tables members and entries. For our purposes members has two columns member_id, member_name, and entries has entry_id, channel_id, author_id. Author_id is an FK to member_id.

I want a count of entries by each author and do it in one SQL statement. So I need a list of the member id's first then take each member id and query the entries table for the count of entries where author_id = member_id.

Programmatically it would look something like this (not full code obvi):

$mems = select member_id from members_table;
foreach($mems as $cur_mem) {
    $entry_count = select count(*) from entries_table where author_id=$cur_mem[member_id];
    echo $entry_count;
}

This is for MySQL and using a CMS called ExpressionEngine, which is the cause of this headache of a solution. Having a hard time describing this issue, so I can clarify any questions.

Thanks!

Answer 1

Try this query:

SELECT member_id, 
       COUNT(author_id ) 
FROM   members_table m 
       INNER JOIN entries_table 
         ON author_id = member_id 
GROUP  BY member_id