0

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

2
Contributors
2
Replies
3
Views
7 Years
Discussion Span
Last Post by nschessnerd
0

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.

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.