MySQL query help...

[rysolag]

There are three tables that look as follows:

TABLE 1
--------
name: memblogs
columns: m_id, name

TABLE 2
--------
name: entries
columns: e_id, m_id

TABLE 3
--------
name: comments
columns: c_id, e_id, m_id

I need to pull all the 'names' from the memblogs table with a COUNT of how many entries and comments each name has. so an example result would be:

name - numEntries - numComments

name1 - 5 - 0
name2 - 10 - 1
name3 - 0 - 2
...

thanks for any help

[mincklerstraat]

you already know you need a COUNT in your syntax - what you also need is a JOIN. Check out sql syntax on JOIN on the site of your db of choice.

[rysolag]

What I have so far is this:

SELECT m.name, COUNT(e.m_id) AS ecount, COUNT(c.m_id) AS ccount
FROM memblogs m LEFT JOIN entries e ON m.m_id=e.m_id
LEFT JOIN comments c ON m.m_id=c.m_id
GROUP BY (m.m_id)

The correct number of rows are return but the two count columns(ecount and ccount) are both the same number; they are both the product of the two count columns that SHOULD be returned.

For example:

If the result I was seeking is 11 entries and 3 comments, then with the current above incorrect query we would get 33 entries and 33 comments.

Any help would be greatly appreciated.

Ryan