-3

I have two table in my same DB

create table a(gr_code nvarchar, code int)
insert into a values('1',100),('0',200),('1',200),('0',100)

create table b(gr_code nvarchar, code int)
insert into b values('1',100),('0',200)

find the code in table A which does not have in table B for particular gr_code

 expected result:
 gr_code    code
 1          200
 0          100
Chanukya
  • 5,833
  • 1
  • 22
  • 36
Rojelo
  • 88
  • 6
  • 1
    Possible duplicate of [SQL query to find record with ID not in another table](https://stackoverflow.com/questions/12048633/sql-query-to-find-record-with-id-not-in-another-table) – Dmitry Sep 20 '17 at 09:33

4 Answers4

1

It's quite simple using the clause exists

select * 
from a 
where not exists (select * 
                  from b 
                  where b.gr_code = a.gr_code and 
                        b.code = a.code)

This returns the result on your sample.

Marc Guillot
  • 6,090
  • 1
  • 15
  • 42
1

Use LEFT JOIN

select a.* 
from a 
left join b on a.gr_code = b.gr_code and a.code = b.code
where b.gr_code is null
AB_87
  • 1,126
  • 8
  • 18
0

You can use LEFT JOIN like this:

SELECT a.*
FROM a
LEFT JOIN b on a.gr_code = b.gr_code
WHERE b.gr_code IS NULL
0

You can use EXCEPT like so:

select * 
from a 
except  
select * 
from b
Tanner
  • 22,205
  • 9
  • 65
  • 83
GDK
  • 1
  • 2