Return value only when values don’t exists in main table

Transact-SQL
1

I have the below tables . Table 1has all the primary values and should be retained. Table 2 can have the same ID in table 1 and same codes when this occurs table 1 primary values should be returned .only return table 2 values when ID are same and codes are different, table 2 primary value is always 0

Table 1
ID,codes,row,primary,type
2,23,3,1,customer
2.54,6,1,customer

Table 2

ID,codes,row,primary,type
2,23,3,0,customer
2,24,6,0,customer

Expected result

ID,codes,row,primary,type
2,23,3,1,customer
2,24,6,0,customer
2.54,6,1,customer

2

hi hope this helps :slight_smile:

please click arrow to the left for drop create data script 
create table Table1 (ID int,codes int,row int ,primary1 int ,type varchar(100) ) 
create table Table2 (ID int,codes int,row int ,primary1 int ,type varchar(100) ) 

insert into table1 values (2,23,3,1,'customer')
insert into table1 values (2,54,6,1,'customer')

insert into table2 values (2,23,3,0,'customer')
insert into table2 values (2,24,6,0,'customer')

select 'SQL Output' ,* from table1 
union all 
select 'SQL Output' ,
    table2.* 
from 
    table2 
where  
   cast(table2.id as varchar(10)) + cast(table2.codes  as varchar(10)) 
      not in 
         ( select 
		      cast(table1.id as varchar(10)) + cast(table1.codes  as varchar(10)) 
		   from 
		      table1 )

image
image

image

3

Thx this worked

4

can use a full outer join

select 'SQLOutput',
		IsNull(t1.Id, t2.id) as ID, 
		IsNull(t1.Codes, t2.Codes) as Codes, 
		IsNull(t1.[Row], t2.[Row]) as [Row], 
		IsNull(t1.[Primary1], t2.[Primary1]) as [Primary1],
		IsNull(t1.[Type], t2.[Type]) as [Type]
 from #Table2 t2
	full outer join #Table1 t1
		on t2.id = t1.id
		and t2.codes = t1.codes
order by 2, 3
5

What about if you add this row:

insert into table2 values (22,3,3,1,'customer')

6

As a bit of a sidebar, you have to be careful with IN and NOT IN. If the "in" list has a NULL in it, it can cause a serious miss or unintended hit. Using WHERE EXISTS or WHERE NOT EXISTS is a much safer bet and can have a pretty good performance advantage, as well.

1 Like
7

Best practice.