CODEWITHSUNDEEP
mysqlselect
mrmoment
mrmoment

Reputation: 757

MySQL select different values as different columns in a same table?

I use a table Votes to store the likes/dislikes for user posts (Posts). Now I want to select the number of a post likes and dislikes. Currently the query is as below:

SELECT p.*, count(v1.id) AS Likes, count(v2.id) AS Dislikes FROM Posts p
LEFT JOIN Votes v1 ON v1.post_id=p.id AND v1.status=1
LEFT JOIN Votes v2 ON v2.post_id=p.id AND v2.status=2
WHERE p.id=123

Is there a better approach that the two cases can be combined into one?

Upvotes: 0

Views: 22

Answers (2)

Adriaan Stander
Adriaan Stander

Reputation: 166486

You could use CASE and SUM in a single JOIN

Something like

SELECT  p.*, 
    SUM(CASE WHEN v1.status=1 THEN 1 ELSE 0 END) AS Likes, 
    SUM(CASE WHEN v1.status=2 THEN 1 ELSE 0 END) AS Dislikes FROM Posts p
LEFT JOIN Votes v1 ON v1.post_id=p.id 
WHERE p.id=123

Upvotes: 1

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44864

You may use conditional sum for this

select
SELECT p.*,
sum(v.status=1) as `Likes`,
sum(v.status=2) as `Dislikes`
from Posts p
LEFT JOIN Votes v ON v.post_id=p.id 
where p.id=123

Upvotes: 0

Related Questions