Click here to Skip to main content
15,868,164 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi,

i have two tables one for lets take A table it has one column called 'id' and it has row values like 1,2,3,4,5. and B table has column 'id' and it has row values like 3,4,
and i want to display the output like only 1,2,5.

Table A Table B
id id
1 3
2 4
3
4
5


output:

id
1
2
5




i am using 'Not in'.but i didnt get exact solution can any body tell me the answer.

Thanks in advance
Posted
Updated 28-Feb-20 1:59am

Try:
SQL
SELECT 
   TableA.Id
FROM 
   TableA
WHERE
   TableA.Id NOT IN 
    (
       SELECT TableB.Id FROM TableB
    )
 
Share this answer
 
Comments
[no name] 6-Jul-12 1:17am    
5!
Prasad_Kulkarni 6-Jul-12 1:22am    
My 5!
Ganesh_mca 6-Jul-12 1:27am    
ok thnaks.. i got it...
Prasad_Kulkarni 6-Jul-12 1:27am    
Then formally 'Accept solution'
Rahul Rajat Singh 6-Jul-12 1:36am    
this solution is ok but my doubt here is that what if the ID field is same in two tables but data field is different. I don't think this will work then. we will have to have a all fields check in where clause.

why not use the except keyword instead. it will check with all the fields. I think it is more efficient oo as its sole purpose is to do what was desired in this question. check my answer.
I think you can do this by using the except keyword.

here is what i tried and it worked

SQL
select * from TableA 
except
select * from TableB
 
Share this answer
 
Comments
Prasad_Kulkarni 6-Jul-12 1:35am    
Good one Rahul!
Rahul Rajat Singh 6-Jul-12 1:37am    
thanks you. i also have some comments on accepted answers. check above.
Sandeep Mewara 6-Jul-12 1:53am    
My 5 too! Good answer.
Ganesh_mca 6-Jul-12 1:54am    
realy simple and good one.thanks rahul
Rahul Rajat Singh 6-Jul-12 1:57am    
you are welcome.
Try this:
SQL
SELECT * FROM TableB T2
WHERE NOT EXISTS 
(SELECT * FROM TableA T1 WHERE T1.ID = T2.ID)
 
Share this answer
 
v2
Comments
Rahul Rajat Singh 6-Jul-12 1:38am    
+5 for the solution/
Prasad_Kulkarni 6-Jul-12 1:43am    
Thank you Rahul!
Sandeep Mewara 6-Jul-12 1:54am    
Looks like someone voted one to you just because circular closing bracket is missing! :doh:
Doesn't matter, looks like he was a low rep guy.
Prasad_Kulkarni 6-Jul-12 2:00am    
Yup, updated now! :)
Consider two tables A , B . The table A has the following fields like ID , Product_Key,
table B has Product_Key.

Table A Table B
ID Product_Key Product_Key
1 05 05
2 06 06
3 07 07
4 09 08
02
Goal is to get the unmatched ID and Product_Key by using this two tables .
Output will be like :
ID Product_Key
1 05
2 06
3 07

Solution :
SELECT a.ID ,
a.Product_Key
FROM A
WHERE Product_Key NOT IN
(
SELECT a.Product_Key
FROM A AS a
INNER JOIN B AS b
ON A.Product_Key = B.Product_Key
)
 
Share this answer
 
Comments
CHill60 28-Feb-20 9:03am    
Reason for my downvote: Your scenario is not at all clear but mainly because that subquery can be simplified, and when you do that you get Solution 1 from over 7 years ago.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900