I need to select from a table where some records may be in two different groups. For instance, in this list, I need to select only those names who are exclusive in the particular group "A".

Grp   Name
A   John
B   Bob
A   Bob
A   Peter
B   James

So, I would only get John and Peter, since Bob belongs to "A" and "B".

I've tried this but it looked a little bit too much having to select twice the table:

SELECT Name FROM TableList
WHERE Grp = 'A'
 AND Name NOT IN (SELECT Name FROM TableList WHERE Grp <> 'A')

Can anyone suggest me a better solution?

Tks,

Marcio Ehrlich

Marcio,

<< I need to select from a table where some records may be in two different
groups. For instance, in this list, I need to select only those names who
are exclusive in the particular group "A". >>

You could also use EXCEPT:

SELECT Name FROM TableList
WHERE Grp = 'A'
EXCEPT
SELECT Name FROM TableList
WHERE Grp <> 'A'

Tim Young
Elevate Software
www.elevatesoft.com

It looks like there is no way of avoiding both selections. I'll try to learn more about "EXCEPT".

Thank you, Tim.

M.

"Tim Young [Elevate Software]" wrote:

SELECT Name FROM TableList
WHERE Grp = 'A'
EXCEPT
SELECT Name FROM TableList
WHERE Grp <> 'A'

Marcio,

<< It looks like there is no way of avoiding both selections. I'll try to
learn more about "EXCEPT". >>

No, you can't avoid that.  It's really a matter of which syntax you prefer.

Tim Young
Elevate Software
www.elevatesoft.com