[SOLVED] Wrong number of returned rows.

[The Little Guy]

Why does this only return one result:

 

SELECT *, t.id as tid, t.title as tTitle, COUNT(r.id) as 'replyCount' FROM 
topics t LEFT JOIN replies r ON (r.topic_id = t.id) 
LEFT JOIN users u ON (u.id = t.owner) 
WHERE t.boardID = '6'
LIMIT 30

 

 

And why does this return two results:

 

SELECT *, t.id as tid, t.title as tTitle FROM 
topics t LEFT JOIN boards b ON (t.boardID = b.id) 
LEFT JOIN users u ON (u.id = t.owner) 
WHERE t.boardID = '6'
LIMIT 30

 

I would like to use something like the first one so I can get a count, but I would like it to return the two results like in the second one. Any Help?

[fenway]

The first one can return nonsense, since you're using an aggregate function without a proper group by clause -- this has two effects: (1) you'll only get back one row since you're group by "all" and (2) the column values will be utter garbage.

 

You may have to use a subquery.

[The Little Guy]

Sweet, it was because I didn't add the "group by"!

 

Thanks again!