Rajesh Rajesh - 7 months ago 16
SQL Question

multiple columns together in where clause

I have one table subscription like below

Subscription

user user_id cve_id vulnerability_id
------- ---------- ------------- -------------------
kumar 17 CVE-2016-3987 74
rajesh 16 CVE-2016-3987 74


I have another table like below

APP_USER_ID VULNERABILITY_ID STATUS
-------------- ------------------- ---------
16 74 assigned


I want to retrive all subscriptions which is not assigned to a user so far. I tried the below query

select * from subscription where user_id not in (select APP_USER_ID from app_user_subscription )


But it is skipping all the subscription if the user matches. I understood why it happened. Because I simply mentioned only user_id. I want to skip based on user_id and cve_id. How I need to update my query?

Answer

By using a LEFT OUTER JOIN on the tables all rows will be returned, you can filter the unallocated ones by just returning rows with a null value using a WHERE clause

SELECT a.* FROM user u 
LEFT OUTER JOIN APP_USER_ID a on a.APP_USER_ID = u.user_id
WHERE u.user_id IS NULL
Comments