How To Compare Records Within The Same Table And Find Missing Records

Here is a simplified version of my table Name Vlan Switch 1 1 Switch 1 2 Switch 1 3 Switch 2 1 Switch 2 2 I want to compare all vlans belonging to switch 1 wit

Solution 1:

This will give you what you're after. It doesn't make any assumptions about the data and will give all missing records. If you want to limit it to just 'Switch 1' then add this to the WHERE clause.

SELECT
  t1.Name,
  t1.Vlan
FROM t t1
WHERENOTEXISTS (SELECT1FROM t t2
                   WHERE t2.Name <> t1.Name
                     AND t2.Vlan = t1.Vlan)

CREATETABLE t 
(
  Name VARCHAR(10),
  Vlan INT
)


INSERTINTO t VALUES('Switch 1',1)   
INSERTINTO t VALUES('Switch 1', 2)
INSERTINTO t VALUES('Switch 1', 3)
INSERTINTO t VALUES('Switch 2', 1)
INSERTINTO t VALUES('Switch 2', 2)

Solution 2:

Using MS SQL Server. Check this working code on SQL Fiddle

SELECT T1.Name, T1.Vlan
  FROM yourTable T1
 WHERENOTEXISTS (SELECT1FROM yourTable T2
                    WHERE T2.Vlan = T1.Vlan
                      AND T2.Name <> T1.Name)

Solution 3:

This should work.

select 
  t1.Name
  ,t1.Vlan
fromtable t1
leftjointable t2 
  on t1.Vlan = t2.Vlan 
 and t1.Name='Switch 1'and t2.Name ='Switch 2'where  t2.Name isnullunionselect 
  t1.Name
  ,t1.Vlan
fromtable t1
leftjointable t2 
  on t1.Vlan = t2.Vlan 
 and t1.Name='Switch 2'and t2.Name ='Switch 1'where  t2.Name isnull

Solution 4:

SELECT*FROM yourTable 
WHERE [Name] ='Switch1'AND [Vlan] NOTIN(SELECT [Vlan] FROM yourTable WHERE [Name] ='Switch2')


Name    Vlan
Switch1 3