SQL Query INNER JOIN with three tables

Question

There are these three tables:

posts
posts_replies
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

Left Join not inner join. If you want it whether there is match or not — Tony Hopkinson
– Tony Hopkinson, Commented Feb 24, 2013 at 17:41
If you want to get rows from tables where there is no match, you need either a left or right join depending on which side the table is on from where you want to return the null records. — steoleary
– steoleary, Commented Feb 24, 2013 at 17:42

Achrome · Accepted Answer · 2013-02-24 17:42:11Z

1

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. :)

answered Feb 24, 2013 at 17:42

Achrome

7,82114 gold badges38 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Query INNER JOIN with three tables

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related