3 table join problem - help!!

I have 3 tables:

- users (userid, username)
- articles (userid, articles)
- comments (userid, comments)

Now I need whole user list with count of articles and count of comments posted by each user (0 in case if not posted any thing)

I am using query like:

SELECT u.*, count(p.userid), count(c.userid) FROM (articles p RIGHT JOIN users u ON p.usereid=u.userid),(users u1 LEFT JOIN comments c ON c.userid=u1.userid) GROUP BY u.userid

but it returns wrong count from articles and comments and if I remove comments table join then it work fine.

Please suggest how to get data from all 3 tables in one go?

 

Comments should contain a ArticleID column so they can therefor be associated to the appropriate article. Is this not the case?

 

Yes it has an articleID field, I mentioned those only which I am using in query.... please suggest if any change needs to be made in table structures.

Relations are based on:

- A user can write many articles
- A user can comment on many articles but only one comment allowed per article
- A non user can also comment by email (which is also userid)

I need records like:

User ID Total Articles Total Comments
-----------------------------------------------------------
10 50

 

Also total number of comments can only be tracked by userid (email) in comments table

 

Do you also have a CommentID field in the comments table?

If so, this will work for you:

SELECT u.userid, COUNT(DISTINCT a.articleid) as ArticleCount, COUNT(DISTINCT c.commentid) AS CommentCount FROM users u LEFT OUTER JOIN articles a ON u.userid = a.userid LEFT OUTER JOIN comments c on u.userid = c.userid
GROUP BY u.userid

 

I've used sub queries on occasion, ugly as hell but sometimes they do get the job done.

Also check out SQL Views, might be helpful in your case.

 

Thanks for all help - this has solved my purpose:

http://www.sqlrecipes.com/sql_questions_answers/do_two_joins_single_query-39/