Group by Not in the Group

Hi,
I am trying to work out a way of finding out if certain values do not appear
in the group by e.g.
ID Ref
1 1
1 2
2 3
2 2
3 1
3 1
I would only want to return ID 1 and 3 as these are the only ID's that dont
have a Ref of 3, if that makes any sense, does anyone know how this is done.
Thanks
PD
Try this:
SELECT DISTINCT [ID] FROM [Test] WHERE [ID] NOT IN (SELECT DISTINCT [ID]
FROM [Test] WHERE [Ref] = 3)
Regards,
JayAchTee
"Phil" wrote:

> Hi,
> I am trying to work out a way of finding out if certain values do not appear
> in the group by e.g.
> ID Ref
> 1 1
> 1 2
> 2 3
> 2 2
> 3 1
> 3 1
> I would only want to return ID 1 and 3 as these are the only ID's that dont
> have a Ref of 3, if that makes any sense, does anyone know how this is done.
> Thanks
> PD