count issue on join

[nschessnerd]

Hi, I have a query:

SELECT h.id, h.creator uid, h.haiku, h.title, h.likes,
h.approved, cat.name category, count(c.id) comms, h.anonymous, 
CASE h.anonymous
WHEN 1
THEN 'Anonymous'
ELSE u.username
END username
FROM haiku h left join categories cat on cat.id= h.category
left join comments c on c.haiku=h.id
left join users u on u.id=h.creator
where h.approved!=0

I currently have two rows in the table but it only returns one, unless i take out count(c.id) as such:

SELECT h.id, h.creator uid, h.haiku, h.title, h.likes,
h.approved, cat.name category, h.anonymous, 
CASE h.anonymous
WHEN 1
THEN 'Anonymous'
ELSE u.username
END username
FROM haiku h left join categories cat on cat.id= h.category
left join comments c on c.haiku=h.id
left join users u on u.id=h.creator
where h.approved!=0

it returns both rows (even though i left in the comments join)
what is causing this and how do i fix it?

Thanks
crasx

[genevish]

Hi, I have a query:

SELECT h.id, h.creator uid, h.haiku, h.title, h.likes,
h.approved, cat.name category, count(c.id) comms, h.anonymous, 
CASE h.anonymous
WHEN 1
THEN 'Anonymous'
ELSE u.username
END username
FROM haiku h left join categories cat on cat.id= h.category
left join comments c on c.haiku=h.id
left join users u on u.id=h.creator
where h.approved!=0

Try adding "GROUP BY h.id" before the where clause.

[nschessnerd]

that would do it... thanks!