SQLTeam.com | Weblogs | Forums

Query Help


#1

I have Data set like this

create table #Data(BID int, AID int, DID int, OV varchar(50))
insert into #Data
values (1, 10, 415, 'CP_(IKMU)(FFFF_C2C_1)'),
(1, 10, 416, 'OO'),
(1, 20, 415, 'CP
(IKMU)(FFFF_C2C_2)'),
(1, 20, 416, 'OO'),
(1, 70, 415, 'CP
(IKMU)(FFFF_C2C_7)'),
(1, 70, 416, 'OO'),
(2, 30, 415, 'CP
(IKMU)(FFFF_C2C_3)'),
(2, 30, 416, 'OO'),
(2, 40, 415, 'CP
(IKMU)(FFFF_C2C_1)'),
(2, 40, 416, 'OO'),
(3, 50, 415, 'CP
(IKMU)(FFFF_C2C_2)'),
(3, 50, 416, 'OO'),
(4, 60, 415, 'CP
(IKMU)_(FFFF_C2C_6)'),
(4, 60, 416, 'OO')

select * from #data d
where d.BID = 1
and not exists ( select * from #data id
where id.BID <> d.BID
and id.OV = d.OV and id.DID = d.DID
)

My Expected result is two rows but its returning one row. how to get these two rows as result set
(1, 70, 415, 'CP_(IKMU)_(FFFF_C2C_7)'),
(1, 70, 416, 'OO'),

Thanks


#2

I don't know, since the 416,'OO' result violates the NOT EXISTS condition. For example, both these rows:
(2, 40, 416, 'OO'),
(3, 50, 416, 'OO'),
would make the "NOT EXISTS" not true. Do you have different rules you need to use?