CODEWITHSUNDEEP
mysql
Coding With Toms
Coding With Toms

Reputation: 35

Left outer join with select where value in right table does not return all rows from left

I have a polymoprhic relationship table "likes" in which I store likes for the tables "lists", "articles", "comments".

The structure of the table is as follows:

I am trying to select all my lists with their like counts:

SELECT lists.*, COUNT(DISTINCT likes.id) AS likes FROM lists
                                      LEFT OUTER JOIN likes ON (lists.id = likes.content_id)
                                      WHERE likes.content_type = 'list'
                                      GROUP BY lists.id

However, it only returns results from the list table where there are likes in the likes table.

Thank you for your help!

Upvotes: 1

Views: 98

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133360

if you need left join you must inclue the where condition in on clause otherwise i used as inner join 

SELECT lists.*, COUNT(DISTINCT likes.id) AS likes 
FROM lists
LEFT OUTER JOINlikes ON 
    (lists.id = likes.content_id 
          AND likes.content_type = 'list')  
GROUP BY lists.id

Upvotes: 2

Related Questions