[Resolved] Query requires irrelevant data

[ScribeOfTheNile]

Hi,

I have the query below, designed to pull my news posts, and to count the number of comments attached to each one. The problem is, if the comments table is empty, the whole thing returns false.
I'm temporarily working around it by having a dummy comment, but it's getting quite annoying. Does anyone have any idea how I can fix my query?

Thanks,
Michael

SELECT n.id as post_id, COUNT(IF(c.postid = n.id, 1, NULL)) as comments_count, n.poster, n.title, n.content, n.type, n.timestamp, t.id, t.type as type_name, u.username FROM news n, news_types t, users u, news_comments c WHERE n.type = t.id AND n.poster = u.id GROUP BY n.id ORDER BY n.id DESC LIMIT 5;

[nemonoman]

you need to do an OUTER JOIN to the comments table. The inner join you are using will return a null vulue if there are no comments. TRY

SELECT n.id as post_id, COUNT(c.postid) as comments_count, n.poster, n.title, n.content, n.type, n.timestamp, t.id, t.type as type_name, u.username FROM news n, news_types t, users u OUTER JOIN news_comments c ON c.postid = n.id WHERE n.type = t.id AND n.poster = u.id GROUP BY n.id ORDER BY n.id DESC LIMIT 5;

[nemonoman]

you need to do an OUTER JOIN to the comments table. The inner join you are using will return a null vulue if there are no comments. TRY

SELECT n.id as post_id, COUNT(c.postid) as comments_count, n.poster, n.title, n.content, n.type, n.timestamp, t.id, t.type as type_name, u.username FROM news n, news_types t, users u OUTER JOIN news_comments c ON c.postid = n.id WHERE n.type = t.id AND n.poster = u.id GROUP BY n.id ORDER BY n.id DESC LIMIT 5;

[nemonoman]

you need to do an OUTER JOIN to the comments table. The inner join you are using will return a null vulue if there are no comments. TRY

SELECT n.id as post_id, COUNT(c.postid) as comments_count, n.poster, n.title, n.content, n.type, n.timestamp, t.id, t.type as type_name, u.username FROM news n, news_types t, users u OUTER JOIN news_comments c ON c.postid = n.id WHERE n.type = t.id AND n.poster = u.id GROUP BY n.id ORDER BY n.id DESC LIMIT 5;

[nemonoman]

you need to do an OUTER JOIN to the comments table. The inner join you are using will return a null vulue if there are no comments. TRY

SELECT n.id as post_id, COUNT(c.postid) as comments_count, n.poster, n.title, n.content, n.type, n.timestamp, t.id, t.type as type_name, u.username FROM news n, news_types t, users u OUTER JOIN news_comments c ON c.postid = n.id WHERE n.type = t.id AND n.poster = u.id GROUP BY n.id ORDER BY n.id DESC LIMIT 5;

[nemonoman]

you need to do an OUTER JOIN to the comments table. The inner join you are using will return a null vulue if there are no comments. TRY

SELECT n.id as post_id, COUNT(c.postid) as comments_count, n.poster, n.title, n.content, n.type, n.timestamp, t.id, t.type as type_name, u.username FROM news n, news_types t, users u OUTER JOIN news_comments c ON c.postid = n.id WHERE n.type = t.id AND n.poster = u.id GROUP BY n.id ORDER BY n.id DESC LIMIT 5;

[nemonoman]

you need to do an OUTER JOIN to the comments table. The inner join you are using will return a null vulue if there are no comments. TRY

SELECT n.id as post_id, COUNT(c.postid) as comments_count, n.poster, n.title, n.content, n.type, n.timestamp, t.id, t.type as type_name, u.username FROM news n, news_types t, users u OUTER JOIN news_comments c ON c.postid = n.id WHERE n.type = t.id AND n.poster = u.id GROUP BY n.id ORDER BY n.id DESC LIMIT 5;

[nemonoman]

you need to do an OUTER JOIN to the comments table. The inner join you are using will return a null vulue if there are no comments. TRY

SELECT n.id as post_id, COUNT(c.postid) as comments_count, n.poster, n.title, n.content, n.type, n.timestamp, t.id, t.type as type_name, u.username FROM news n, news_types t, users u OUTER JOIN news_comments c ON c.postid = n.id WHERE n.type = t.id AND n.poster = u.id GROUP BY n.id ORDER BY n.id DESC LIMIT 5;

[PABobo]

i think he wants you to use an outer join.