[RESOLVED] Combining multiple queries to make one simple query.

HairyArse

Hi guys

I'm trying to combine 3 queries but having difficulties getting the results I want.

the query is:

SELECT 

a. * , 
COUNT( b.comments ) AS comments_count, 
COUNT( c.reply_text ) AS post_count

FROM 

tbl_users a, 
tbl_comments b, 
tbl_forum_replies c

WHERE 
a.user_id = 1
AND b.user = 1
AND c.reply_author = 1

GROUP BY a.user_id

THe problem is with the two COUNT queries I'm adding, if I add only one of them, I get the right results but as soon as I add the second query I get a number for both counts that is correct for neither of them.

Any ideas what's wrong with my query?

Piranha

is b.user and c.reply_author references to a table? If it is then try this:

WHERE
a.user_id = 1
AND b.user = a.user_id
AND c.reply_author = a.user_id

HairyArse

Hi Piranha

a.user_id
b.user
c.reply_author

are all linked to the same field within the table tbl_users, but even if I change the query to all reference a.user_id I still get the incorrect results.

It's really puzzling as like I said already, if I just have the one count in the query the results are correct but as soon as I add a second count, I get an incorrect count for both fields.

Piranha

It may be because you are joining the tables in the FROM part and not in JOIN parts. Test this:

SELECT

a. * ,
COUNT( b.comments ) AS comments_count,
COUNT( c.reply_text ) AS post_count

FROM

tbl_users a

LEFT JOIN // maybe it should be INNER JOIN instead
comments b ON a.id = b.user_id
LEFT JOIN forum_replies c ON a.id = c.user_id

WHERE
a.user_id = 1

GROUP BY a.user_id

If this does not work I have to start the database up to solve it since it is a problem that I'm sure is solvable. But I need to know exactly what is wrong, not only that something is not wrong. Please post example data, what you expect to get and what you really get.

HairyArse

Unfortunately that didn't work either.

For the counts I am expecting to the query to output 68 and 264 but instead for both fields it outputs 17952 which curiously enough is 68 * 264.

Any ideas why it's doing that?

Piranha

Of course it is doing that. Why didn't I think about that earlier?

Since the query joins 3 tables it is not strange at all that the result is what you get. As I said before I will try to solve it, just give me some time for it.

Piranha

I can't see any way to do it without using a subquery. With a subquery you can do like this:

SELECT
a. * ,
COUNT( b.comments ) as comments_count,
(SELECT COUNT(c.reply_text) FROM tbl_forum_replies c WHERE c.reply_author = 1 GROUP BY c.reply_author) as post_count

FROM
tbl_users a,
tbl_comments b

WHERE
a.user_id = 1
AND b.user = a.user_id

GROUP BY a.user_id

HairyArse

Well it works with a subquery which is great news as far as I'm concerned. Surely one query with a sub query is more efficient than 3 individual queries right?

🙂