Combining IN and NOT IN in SQL as single result
- by UltraVi01
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
