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

Hi

Hope this helps

Another way of doing this

create data script

drop table #temp

create table #temp (Type varchar(1) , ID int , Sub_ID int , Data varchar(100) )
insert into #Temp select 'M', 1000, 10,' {stuff}'
insert into #Temp select 'M', 1000, 20,' {stuff}'
insert into #Temp select 'M', 1000, 30,' {stuff}'
insert into #Temp select 'M', 1000, 40,' {stuff}'
insert into #Temp select 'W', 1000, 10,' {stuff}'
insert into #Temp select 'W', 1000, 20,' {stuff}'
insert into #Temp select 'W', 1000, 30,' {stuff}'
insert into #Temp select 'W', 1000, 35,' {stuff}'
insert into #Temp select 'W', 1000, 36,' {stuff}'
insert into #Temp select 'W', 1000, 40,' {stuff}'

select * from #temp where Type = 'W' 
except 
select replace(Type,'M','W'),ID , Sub_ID , Data from #temp where Type = 'M'

image