CODEWITHSUNDEEP
mysqlsql
Andre Chenier
Andre Chenier

Reputation: 1186

MySQL query simultaneously combined result from 2 tables (fetch if count() is zero also)

I have 2 posts in my table that can be published on homepage. First has 3 comments and the other has no comments. Query below gives 1 result row but I want 2 result rows.

Expected result of the query

  1. fetch requested rows from blog table
  2. fetch COUNT of comments for the related post id. IF no comment is available then output should be 0.

Simplified form of my 2 tables

structure               values                  expected query output   current query output
====================    ====================    =====================   =====================
blog    |   comments    blog    |   comments    1 - body1 - 3 comments  1 - body1 - 3 comments
--------------------    --------------------    2 - body2 - 0 comments
id      |   commentid       1   |   1
body    |   comment     body1   |   comment1
            postid          2   |   1
                        body2   |   2
                                |   comment2
                                |   1
                                |   3
                                |   comment3
                                |   1

My query doesn't work with this form?

Can you please correct me.

$query = 'SELECT 
blog.id, 
blog.body, 
COUNT(comments.postid)
FROM 
blog, comments 
WHERE 
blog.status="publish" AND comments.postid = blog.id 
ORDER BY blog.id DESC';

Upvotes: 1

Views: 118

Answers (4)

Reisclef
Reisclef

Reputation: 2148

At a first glance, you may need a left join:.

Edit: Confirmed. Have a look at this: http://sqlfiddle.com/#!2/1475ab/1

$query = 'SELECT 
b.id, 
b.body, 
COUNT(c.comment)
FROM blog b
left join comments c
on b.id = c.postid
WHERE blog.status="publish"
group by b.id
ORDER BY b.id DESC';

Upvotes: 1

Chris Charles
Chris Charles

Reputation: 4446

Firstly to clarify your tables, is this what you have:

Table: blog
id | body
---+-------
1  | body1
2  | body2

Table: comments
commentid | comment  | postid
----------+----------+---------
1         | comment1 | 1
2         | comment2 | 1
3         | comment3 | 1

So the problem with you query is the its an inner join, only returning results where rows exist in both tables.

You need to use a left join from blog to comment.

Looks like I've already been beaten to the actual sql though, so I'll leave that to other answers.

Upvotes: 1

Ankit Bajpai
Ankit Bajpai

Reputation: 13517

Use left join here, Such as:-

SELECT blog.id, blog.body, COUNT(comments.postid)
FROM blog LEFT JOIN comments 
ON comments.postid = blog.id 
WHERE blog.status="publish" 
GROUP BY blog.id, blog.body
ORDER BY blog.id DESC';

Upvotes: 1

Andomar
Andomar

Reputation: 238186

A left join returns blogs even though they have no matching comment:

select  b.id
,       b.body
,       count(c.commentid)
from    blog b
left join
        comments c
on      b.id = c.blogid
where   b.status = 'publish'
group by
        b.id
,       b.body
order by
        b.id desc

Upvotes: 1

Related Questions