Find the extra rows

Transact-SQL
1

Given a table containing data similar to:

TYPE	ID	SUB_ID	DATA
M		1000	10	{stuff}
M		1000	20	{stuff}
M		1000	30	{stuff}
M		1000	40	{stuff}
W		1000	10	{stuff}
W		1000	20	{stuff}
W		1000	30	{stuff}
W		1000	35	{stuff}
W		1000	36	{stuff}
W		1000	40	{stuff}

I want to join on ID and SUB_ID but report out only the odd rows that exist with TYPE = 'W' but not TYPE = 'M'. So, in this case, I would want to report out only

W		1000	35	{stuff}
W		1000	36	{stuff}

I'm not sure how to accomplish this.

2

SELECT W.*
FROM your_table W
LEFT JOIN your_table M ON W.ID = M.ID AND W.SUB_ID = M.SUB_ID AND M.TYPE = 'M'
WHERE W.TYPE = 'W' AND M.ID IS NULL;

3

Wow, thanks!

I don't know why I struggled so much with this one. :slight_smile:

4

or without a join:

WITH TypeCounts
AS
(
	SELECT [Type], ID, Sub_ID, [Data]
		,COUNT(*) OVER (PARTITION BY ID, Sub_ID) AS TypeCount
	FROM YourTable
)
SELECT [Type], ID, Sub_ID, [Data]
FROM TypeCounts
WHERE [Type] = 'W'
	AND TypeCount = 1;