SELECT JOIN MySQL查询中一列的唯一值

Question

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:

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:

Answer 1

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.