SQL - Pull accts that contain only 1 occurrance of a record type

[colezpapa]

I have a table that contains a type field and an NAME_ID field. The NAME_ID can contain 1 to many different types.
It could be type MM, A LM, G .....
I am looking to get a list of only those NAME_ID's that contain only 1 type and that type is equal to "A".

How could I construct that in a query.

 

[PHV]

SELECT NAME_ID
FROM yourTable
GROUP BY NAME_ID
HAVING Count(*) = 1 AND Min(Type) = 'A'

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[lespaul]

do you mean you have:
[tt]
ID Name
1 MM
1 A
1 LM
1 G[/tt]

or you have:

[tt]
ID Name
1 MM, A, LM, G[/tt]



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins