Joining three different tables

Question

I am trying to get the count value of two tables and use the member id as key to count the rows of the other tables, I have this code so far but it is outputting a big value for comCount and empty value for chatCount. Here is my code

SELECT members.*
, comments.commenter_id
, cb.user_id
, COUNT(comments.comment) AS comCount
, COUNT(cb.message) AS chatCount
FROM members 
INNER JOIN comments ON members.id = comments.commenter_id
INNER JOIN chat_box AS cb ON members.id = cb.user_id
WHERE members.id ='$profileId'`

I am sure, before I added the chat_box join it displays the comment count without a problem — kabuto178, Jan 14 '13 at 03:09
that is a query with one join, I get a really big number when i use both joins as used in my questions, http://sqlfiddle.com/#!2/59505/1 — kabuto178, Jan 14 '13 at 03:33
here is my the result of my deleted answer, will I undelete it? http://sqlfiddle.com/#!2/59505/3 — John Woo, Jan 14 '13 at 03:35

score 1 · Accepted Answer · edited May 23 '17 at 12:18

SELECT  members.* , 
        a.commenter_id , 
        b.user_id , 
        a.totalComment , 
        b.totalChat
FROM    members 
        INNER JOIN 
        (
            SELECT commenter_id, COUNT(*) totalComment
            FROM comments
            GROUP BY commenter_id
        ) a ON members.id = a.commenter_id
        INNER JOIN 
        (
            SELECT user_id, COUNT(*) totalChat
            FROM chat_box 
            GROUP BY user_id            
        ) b  ON members.id = b.user_id
WHERE   members.id = '$profileId'

As a sidenote, the query is vulnerable with SQL Injection if the value(s) came from the outside. Please take a look at the article below to learn how to prevent from it. By using PreparedStatements you can get rid of using single quotes around values.

How to prevent SQL injection in PHP?

Thanks JW, it seems there was an extra ' at the end I did not realize till now ^_^ — kabuto178, Jan 14 '13 at 03:44

score 0 · Answer 2 · answered Jan 14 '13 at 03:11

SELECT members.*
, comments.commenter_id
, cb.user_id
,(select  COUNT(1) from comments where commenter_id = members.id) AS comCount
,(select count(1) from chat_box where user_id = members.id) AS chatCount
FROM members 
WHERE members.id ='$profileId'`

Joining three different tables

2 Answers2