Combining IN and NOT IN in SQL as single result

Question

I apologize for the vague title. I am attempting to write a query that returns an alias column with matching values (resulting from an IN) as well as an alias column with values that do not match (using NOT IN). I want the result set to have: userId | matches | nonmatches. I currently have the following query which returns the matches as expected. I am having trouble getting the nonmatches in the result set -- that is, from a NOT IN statement

SET @userId = 9;
SELECT ug.user_id, COUNT(DISTINCT goal_id) as matches
FROM user_goal ug
WHERE ug.user_id!=@userId
AND goal_id IN (SELECT iug.goal_id FROM user_goal iug WHERE user_id=@userId)
GROUP BY user_id ORDER BY matches DESC LIMIT 4

So, the NOT IN would look something like this:

goal_id NOT IN(SELECT uggg.goal_id FROM user_goal uggg WHERE user_id=@userId) AS nonmatches

I am just not sure how to incorporate the NOT IN statement in my query so I get all the results

Answer 1

group it by one another param, fix one param that will be used if there are goals from sub query

Answer 2

I think a UNION is what you need:

SET @userId = 9;

SELECT ug.user_id, COUNT(DISTINCT goal_id) as matches
FROM user_goal ug
WHERE ug.user_id!=@userId
AND goal_id IN (SELECT iug.goal_id FROM user_goal iug WHERE user_id=@userId)
GROUP BY user_id ORDER BY matches DESC LIMIT 4

union all

SELECT ug.user_id, COUNT(DISTINCT goal_id) as matches
FROM user_goal ug
WHERE ug.user_id!=@userId
AND goal_id NOT IN(SELECT uggg.goal_id FROM user_goal uggg WHERE user_id=@userId)
GROUP BY user_id ORDER BY matches DESC LIMIT 4

Answer 3

Try this (could be simplified and further optimized if mysql have CTE though):

select u.user_id, the_matches.matches, the_nonmatches.nonmatches
from user u 
left join 
(    
    SELECT ug.user_id, COUNT(DISTINCT goal_id) as matches
    FROM user_goal ug
    WHERE ug.user_id!=@userId
    AND goal_id IN (SELECT iug.goal_id FROM user_goal iug WHERE user_id=@userId)
    GROUP BY user_id 
    ORDER BY matches DESC LIMIT 4
) as the_matches on the_matches.user_id = u.user_id
left join
(
    SELECT ug.user_id, COUNT(DISTINCT goal_id) as nonmatches
    FROM user_goal ug
    WHERE ug.user_id!=@userId
    AND goal_id NOT IN(SELECT uggg.goal_id FROM user_goal uggg WHERE user_id=@userId) 
    GROUP BY user_id 
) as the_nonmatches on the_nonmatches.user_id = u.user_id