Search no existing sets of records

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Search no existing sets of records

Author

Topic

duf
Starting Member

39 Posts

Posted - 2014-01-19 : 14:04:15

Hi
I need to query the contents: Look in Table A of such sets (A, B), which are not present in table B
To illustrate:
Tab A
A....B
aa..kr - this set does not occur in Tab B
bb..gh
vv..kl
cc..er
ss..we

Tab B
A....B
aa..kw
bb..gh
vv..kl
cc..er
ss..we

Thanks

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-01-19 : 17:08:33

[code]select *
from table1
where not exists(select *
from table2
where table2.field1=table1.field1
and table2.field2=table1.field2
and table2.field3=table1.field3
.
.
.
)[/code]

duf
Starting Member

39 Posts

Posted - 2014-01-19 : 17:37:46

Thank You :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-20 : 06:33:45

there are multiple ways of doing this. some of other methods are


1. 
SELECT A,B
FROM TableA
EXCEPT
SELECT A,B
FROM TableB


2.
  SELECT a.A,a.B
  FROM TableA a
  LEFT JOIN TableB b
  ON a.A = b.A
  AND a.B = b.B
  WHERE b.A IS NULL

3. 
SELECT
FROM TableA a
OUTER APPLY(SELECT A
            FROM TableB
            WHERE A= a.A
            AND B = a.B
            )b
WHERE b.A IS NULL 

4.
SELECT A,B
FROM
(
 SELECT A,B,'A' AS Tab
 FROM TableA
 UNION ALL
 SELECT A,B,'B'
 FROM TableB
)t
GROUP BY A,B
HAVING SUM(CASE WHEN Tab = 'B' THEN 1 ELSE 0 END) = 0

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources