CODEWITHSUNDEEP
phpmysqlsql
Brian Nezhad
Brian Nezhad

Reputation: 6266

Unique value for one columns in SELECT JOIN MySQL query

I have an query where user can pull data from two table with JOIN. but I would like one columns in the result to be specific to the user_id or return null if it doesn't match the user_id.

SQL:

SELECT 
        posts.id, 
        posts.deviceID, 
        posts.type, 
        posts.title,  
        posts.time, 
        SUM(value) AS votes,
        value AS userVote, <--- THIS NEED TO BE UNIQUE TO WHAT THE USER ID IS.          
        FROM posts 
        LEFT JOIN votes 
        on (posts.id = votes.post_id)
        GROUP BY posts.id

As you can see there is SELECT statement of value AS userVote. the function provides user_id with php $_POST['user_id'] method, I know how to input this value. the query is confusing to me a little.

Result in snapshot:

enter image description here

Also, sorry in advance if this is duplicated question, but I can't think of what this method is called in MySQL query, if you can tell me I appreciate it.

UPDATE:

Currently I can get the value correctly with CASE WHEN votes.user_id = 'USERID' THEN value END AS userVote Thanks to one answer, but when there are two values it wouldn't return user value but null.

Update Screenshot:

enter image description here

Upvotes: 0

Views: 76

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

You want conditional aggregation:

SELECT p.id, p.deviceID, p.type, p.title, p.time, 
       SUM(v.value) AS votes,
       MAX(CASE WHEN p.user_id = u.user_id THEN v.value END) as userVote
FROM posts p LEFT JOIN
     votes v
     on p.id = v.post_id
GROUP BY p.id;

Note: if a user could vote multiple times, you might want SUM() or GROUP_CONCAT(), depending on what you want to see.

Upvotes: 2

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49270

SELECT 
posts.id, 
posts.deviceID, 
posts.type, 
posts.title,  
posts.time, 
SUM(value) AS votes,
-- value AS userVote,
case when posts.user_id = votes.user_id then value end as userVote,
case when posts.user_id = votes.user_id then post.user_id else null end AS UserIDVote           
FROM posts 
LEFT JOIN votes 
on (posts.id = votes.post_id)
GROUP BY posts.id

Added a case condition which would work if both the tables have a user_id column. You would get user_id for all the matches and null otherwise.

Edit: If specific values are needed when there are matching userid's, aggregation of values has to be excluded from the query.

SELECT 
posts.id, 
posts.deviceID, 
posts.type, 
posts.title,  
posts.time,
case when posts.user_id = votes.user_id then value end as userVote,
case when posts.user_id = votes.user_id then post.user_id else null end AS UserIDVote           
FROM posts 
LEFT JOIN votes 
on posts.id = votes.post_id

Upvotes: 1

Related Questions