Working with Exists stmnt. 1

[snowcold]

I have multiple user groups. Groups are assigned operations in which they can perform. These 2 particular groups should have equal operations assigned but currently they are not. One group has 501 and the other 504 operations assigned. I thought I could quickly determine which operations I am missing. I am trying this:

select a.op_id from op_groupings a
where a.group_id = 21
and not exists(select b.op_id from op_groupings b
where b.group_id = 11)

oh, group 21 is the one with the correct operations assigned to it. I also realize that I could delete all records for group 11 and perform an insert statement based on group 21 but where is the fun in that?

 

[PHV]

One way:
SELECT op_id FROM op_groupings
WHERE group_id = 21
AND op_id NOT IN (SELECT op_id FROM op_groupings WHERE group_id = 11)
Another way:
SELECT A.op_id
FROM op_groupings A LEFT JOIN op_groupings B
ON A.op_id=B.op_id AND A.group_id=21 AND B.group_id=11
WHERE B.op_id IS NULL

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[snowcold]

Thanks so much...I went with the first one.
Mike