CODEWITHSUNDEEP
sqlsql-serverunique
Benny
Benny

Reputation: 649

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 with all vlans belonging to switch 2 and print out the missing ones in one of the switches using SQL query. Is it possible to do so? Note all data resides inside the same table.

On the example data provided above, the query should return Row 3

Switch 1,  3

Here is the query I tried earlier (my requirement has few more conditions than the simplified version in my query):

Select Vlans.VLANID From VLANS
 JOIN Nodes ON 
VLANS.NodeId = Nodes.NodeID
Where Nodes.sysName LIKE 'SSW010%' and Vlans.VlanID NOT In
(Select Vlans.VLANID AS Vlan2 From VLANS
 JOIN Nodes ON 
VLANS.NodeId = Nodes.NodeID
Where Nodes.sysName LIKE 'SSW001%')

Upvotes: 2

Views: 23099

Answers (4)

Niladri Biswas
Niladri Biswas

Reputation: 4171

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


Name    Vlan
Switch1 3

Upvotes: 1

Yaroslav
Yaroslav

Reputation: 6534

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

SELECT T1.Name, T1.Vlan
  FROM yourTable T1
 WHERE NOT EXISTS (SELECT 1 
                     FROM yourTable T2
                    WHERE T2.Vlan = T1.Vlan
                      AND T2.Name <> T1.Name)

Upvotes: 3

Dave Barker
Dave Barker

Reputation: 6437

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
WHERE NOT EXISTS (SELECT 1 
                    FROM t t2
                   WHERE t2.Name <> t1.Name
                     AND t2.Vlan = t1.Vlan)

CREATE TABLE t 
(
  Name VARCHAR(10),
  Vlan INT
)


INSERT INTO t VALUES('Switch 1',1)   
INSERT INTO t VALUES('Switch 1', 2)
INSERT INTO t VALUES('Switch 1', 3)
INSERT INTO t VALUES('Switch 2', 1)
INSERT INTO t VALUES('Switch 2', 2)

Upvotes: 3

Dumitrescu Bogdan
Dumitrescu Bogdan

Reputation: 7267

This should work.

select 
  t1.Name
  ,t1.Vlan
from table t1
left join table t2 
  on t1.Vlan = t2.Vlan 
 and t1.Name='Switch 1' 
 and t2.Name = 'Switch 2'
where  t2.Name is null
union
select 
  t1.Name
  ,t1.Vlan
from table t1
left join table t2 
  on t1.Vlan = t2.Vlan 
 and t1.Name='Switch 2' 
 and t2.Name = 'Switch 1'
where  t2.Name is null

Upvotes: 1

Related Questions