SQLTeam.com | Weblogs | Forums

How to get mask that have multiple part and different on pin out on same table?

I work on SQL server 2012 I face issue I can't display maskes that have multiple id different on same mask and on same time

every part have pinout different from another part related to same mask then display it .

meaning mask like 1200 below have multi part id and different and pin out as 10 and 5 for parts related to same mask then display or show it .

so I get first masked have multiple part

then check parts inside mask is similar or different

if different then check pin out if is different then display it .

Example
create table #parts
(
MaskId int,
PartId int,
PinOut int
)
insert into #parts(MaskId,PartId,PinOut)
values
(1200,500,10),
(1200,600,5),
(1900,500,10),
(1900,200,7),
(4500,500,3),
(4500,500,1),
(4500,500,5),
(7000,300,5),
(8000,300,5),
(8000,200,5),
(9000,300,14),
(9000,300,7)
Expected result is
|MaskId|PartId|PinOut|

MaskId PartId PinOut
1200 500 10
1200 600 5
1900 500 10
1900 200 7
4500 500 3
4500 500 1
4500 500 5

sample to explain

3 masked report must display as 1200 and 1900 and 4500

i show only above 1200 for explain and clear only

hi

you said if for same mask .. PartId different and then PinOut different then display it ..
In your expected result 4500 is not correct i think .. it has same Part 500
image

i tried to do this .. see if i am correct

select distinct * from 
(
	select a.* from parts a join parts b  on a.MaskId = b.MaskId and a.PartId <> b.PartId and a.PinOut <> b.PinOut
		union all 
	select b.* from parts a join parts b  on a.MaskId = b.MaskId and a.PartId <> b.PartId and a.PinOut <> b.PinOut
) a 

image

yes you are correct this is get expected result
thanks