select only value?

[chaoma]

Hello, could someone help me with the following problem. It seems to be easy, but I couldn't figure out how.


I have the table below (mytable)

p_id type date
1 AC 20050301
1 MK 20050401
1 AC 20050401
2 AC 20050901
2 AC 20050301
3 AC 20050301
3 MK 20050301

I would like to select an ID where type='AC', but no correspondence 'MK'. In this case it should return:

p_id type date
2 AC 20050901
2 AC 20050301

I was thinking of using self-join, but then p_id=2 doesn't have any 'MK' status.

Any help would be appreciate.

 

[manjarikosaraju]

select pid, type, dateP
from #tab1 a
where not exists (select pid from #tab1 b where type = 'MK' and a.pid = b.pid)

 

[chaoma]

Thanks.