Clever Geek Handbook

Compare combination of two columns with combination of other two in SQL

I have 2 tables, tbl_NameCode and tbl_Bkp, having column ids and a name.

I need to select records from tbl_NameCode that are not in tbl_bkp.

SELECT * FROM tbl_Bkp 
WHERE ? */COMBINATION OF ID,Name NOT IN (SELECT COMBINATION OF ID,NAME FROM tbl_NameCode )*/

How do I compare a combination of two columns with a combination of the other two?

+3


source to share


5 answers


If I'm not mistaken, the operator should work. Except 



SELECT Id1,Id2,Id3,..Name FROM tbl_Bkp
Except
select Id1,Id2,Id3,..Name from tbl_NameCode
+5


source


Use LEFT JOIN to fetch all records from the left table and add where condition with a null check of the second table to find unmatched records of the left table

Try the following:



SELECT NC.ID, NC.Name
FROM tbl_NameCode NC 
LEFT OUTER JOIN tbl_bkp A ON NC.ID = A.ID AND NC.Name = A.Name
WHERE A.ID IS NULL;
+2


source


you can also try this:

declare @tbl_NameCode table (id int, name varchar(100))

declare @tbl_Bkp table (id int, name varchar(100))

insert into @tbl_NameCode select 1, 'ABC'
union select 2, 'XYZ'
union select 3, 'PQR'

insert into @tbl_Bkp select 1, 'ABC'
union select 2, 'XXX'
union select 3, 'PQR'

select a.* from @tbl_Bkp a
left join @tbl_NameCode b on convert(varchar, a.id) + a.name = convert(varchar, b.id) + b.name
where b.id is null
+1


source


you can concatenate strings. If your id is an integer then translate it to nvcrchar (50)

SELECT * FROM tbl_Bkp 
WHERE ID+''+Name NOT IN (SELECT cast(ID as nvarchar(50))+''+NAME FROM tbl_NameCode
+1


source


Using SQL EXISTS

will do a good comparison for you. and return results where they differ. Exists are pretty quick and desirable if you have a large entry.

SELECT ID,Name FROM tbl_Bkp t1
 WHERE NOT EXISTS (SELECT ID,Name FROM tbl_NameCode WHERE ID=t1.ID AND Name=t1.Name )
+1


source






More articles:


All Articles