CODEWITHSUNDEEP
mysqlsql
Secure
Secure

Reputation: 153

Get count of rows from LEFT JOIN

Blog table

T

Comment table

Comment table

i'm trying get no of count comment at one blog and if there is no comment they return 0.i'm trying many JOIN but i'm not getting right data.

My query-

SELECT count(cid),     tbl_blog.blog_name,tbl_blog.bid,tbl_blog.blog_images,tbl_blog.blog_content,tbl_blog.created_date
    FROM tbl_blog
     LEFT JOIN tbl_blog_comment
    ON tbl_blog_comment.bid = tbl_blog.bid

Please help.

Upvotes: 1

Views: 78

Answers (1)

Martin
Martin

Reputation: 22760

Count(cid) needs to be be count(TABLE.COLUMN) format.

so try:

count(tbl_blog_comment.cid) as countRows

This is because the query is running over multiple tables but - as far as I understand your question- the count is just a Column but doesn't tell MySQL where the column is.

EDIT: This is updated to reflect comment conversation and to give a more fuller answer:

SELECT tbl_blog.blog_name, tbl_blog.bid, tbl_blog.blog_images, tbl_blog.blog_content, tbl_blog.created_date, COUNT(tbl_blog_comment.cid) as CountComments
    FROM tbl_blog
     LEFT JOIN tbl_blog_comment
    ON tbl_blog_comment.bid = tbl_blog.bid GROUP BY tbl_blog.bid

This will return the count of comments per blog, for each row of your result set, each row corresponding to each blog. The count will be returned in the array variable "CountComments"

Also credit to Mihai for the "Group By" addition.

Upvotes: 1

Related Questions