CODEWITHSUNDEEP
mysqlinner-join
Aamir Siddique
Aamir Siddique

Reputation: 334

SQL Query INNER JOIN with three tables

There are these three tables:

  1. posts
  2. posts_replies
  3. likes

This query is returning me posts and their replies count all fine. SELECT posts.title, posts.num, posts.status, posts.category, posts.content, posts.member_num, COUNT( posts_replies.blyrb_num ) AS count

FROM posts_replies
INNER JOIN posts ON ( posts_replies.blyrb_num = posts.num )
WHERE posts.status =1
AND posts.access = 'Public'
GROUP BY posts.num
ORDER BY count DESC
LIMIT 50

Records this query is returning are: 47

And this is a bit updated query that i wanted to pull the Likes Count of each Reply on Posts.

SELECT posts.title, posts.num, posts.status, posts.category, posts.content, posts.member_num, 
COUNT( posts_replies.blyrb_num ) AS count,
COUNT( likes.comment_num  ) AS likes_count
FROM posts_replies
INNER JOIN posts ON ( posts_replies.blyrb_num = posts.num )
INNER JOIN likes ON ( likes.comment_num = posts_replies.num )
WHERE posts.status =1
AND posts.access = 'Public'
GROUP BY posts.num
ORDER BY count DESC
LIMIT 50

This query is returning Likes Count fine, but not including those records which have no Likes. So the Records this query is returning are: 40

I want to include Likes Count of each reply, even if it has 0 likes.

Any help?

Thank you

Upvotes: 0

Views: 1084

Answers (1)

Achrome
Achrome

Reputation: 7821

Using LEFT JOIN instead of INNER JOIN might help you here

SELECT posts.title, posts.num, posts.status, posts.category,
posts.content,posts.member_num, 
COUNT( posts_replies.blyrb_num ) AS count,
COUNT( likes.comment_num  ) AS likes_count
FROM posts_replies
INNER JOIN posts ON ( posts_replies.blyrb_num = posts.num )
LEFT JOIN likes ON ( likes.comment_num = posts_replies.num )
WHERE posts.status = 1
AND posts.access = 'Public'
GROUP BY posts.num
ORDER BY count DESC
LIMIT 50

The idea of LEFT JOIN is to match rows even if there are none to be matched on the right side. INNER JOIN only works when both sides have rows. :)

Upvotes: 1

Related Questions