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
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
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;
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'