How To Retrieve Data Which Is Not Present In Any Field Of One Table In SQL Server?

I want to retrieve a data which is not present in any field from Table1 For example I have two table 1. #tempLastSold 2. ItemRelation #tempLastSold records ID ExtendedDescrip

Solution 1:

Is this what you want?

select ir.*
from ItemRelation ir 
where not exists (select 1
                  from #tempLastSold tls
                  where tls.id = ir.id
                 ) or
      (ir.childid1 is not null and
       not exists (select 1
                   from #tempLastSold tls
                   where tls.id = ir.childid1
                  )
      ) or
      (ir.childid2 is not null and
       not exists (select 1
                   from #tempLastSold tls
                   where tls.id = ir.childid2
                  )
      ) or
      (ir.childid3 is not null and
       not exists (select 1
                   from #tempLastSold tls
                   where tls.id = ir.childid3
                  )
      );

For performance, you want an index on 3tempLastSold(id).

You can also simplify the query as:

select ir.*, g.which
from ItemRelation ir cross join
     (values (id, 'id'), (childid1, 'childid1'), (childid2, 'childid2'), (childid3, 'childid3')) v(id, which)
where v.id is not null and
      not exists (select 1
                  from #tempLastSold tls
                  where tls.id = ir.id
                 );

This easily shows you which column is missing.

EDIT:

You can also express this using left join. The logic is a little tricky. You need to look for a match and then see where no match occurs. For the id column, this looks like:

select ir.*
from ItemRelation ir left join
     #tempLastSold tls
     on tls.id = ir.id
where tls.id is null;

Your version will find a match for all non-NULL values, because there will always be a record in tls that doesn't match the given (non-NULL) value.

Solution 2:

Using a single left join:

select ir.ID,    
    ir.ChildID1,    
    ir.ChildID2,   
    ir.ChildID3
from [dbo].[ItemRelation] ir
  left join #tempLastSold tLs 
    on tLs.ID = ir.id 
    or tLs.ID = try_convert(int,ChildID1) 
    or tLs.ID = try_convert(int,ChildID2)
    or tLs.ID = try_convert(int,ChildID3)
where tls.id is null;

rextester demo: http://rextester.com/SBA83066

returns:

+----+----------+----------+----------+
| ID | ChildID1 | ChildID2 | ChildID3 |
+----+----------+----------+----------+
|  1 | 1A       | 1B       | 1C       |
|  2 | 2A       | 2B       | 2C       |
|  2 | 3A       | 3B       | 3C       |
+----+----------+----------+----------+

---

using not exists() instead, same result

select ir.ID,    
    ir.ChildID1,    
    ir.ChildID2,   
    ir.ChildID3
from [dbo].[ItemRelation] ir
where not exists (
  select 1
  from #tempLastSold tLs 
  where tLs.ID = ir.id 
     or tLs.ID = try_convert(int,ChildID1) 
     or tLs.ID = try_convert(int,ChildID2)
     or tLs.ID = try_convert(int,ChildID3)
    )

Solution 3:

You can use not in

select * from #tempLastSold where ID not in (select id from ItemRelation Union all Select childid1 from ItemRelation union all Select childid2 from itemrelation union all select childid3 from ItemRelation )

Solution 4:

Your question is not so clear you want to retrieve something that no longer exists at table number 1? By table number 2? Or do you want Table 1 to copy to Table 2 by certain information?