SQLTeam.com | Weblogs | Forums

Self join to find id's that in one type but not in other type

sql2012
sql2008
sql2008r2

#1

Hi ,
I have a table with 2 types 3 & 4 , for each type i have some id for each record, i want to find id's that in type 3 but not in type 4.

This is the data

Type,id
3,1
3,2
3,3
4,3
4,4

The result should be:
3,1
3,2


#2
WITH Counts
AS
(
	SELECT [Type], [id]
		,COUNT(*) OVER (PARTITION BY [id]) AS IDCount
	FROM YourTable
)
SELECT [Type], [id]
FROM Counts
WHERE [Type] = 3
	AND IDCount = 1;

#3
SELECT tn.*
FROM (
    SELECT id
    FROM table_name
    GROUP BY id
    HAVING MAX(CASE WHEN type = '3' THEN 1 ELSE 0 END) = 1 AND
        MAX(CASE WHEN type = '4' THEN 1 ELSE 0 END) = 0
) AS ids_to_list
INNER JOIN table_name tn ON tn.id = ids_to_list.id
WHERE tn.type <> '3'