Select ONLY rows with a value, but do not otherwise appear

[gswan]

I am trying to select the people in a table that exist with a certain value in one field, and do not otherwise appear in the table.

Here’s a sample of the data in the table:

Name IDNum
John 1
John 2
John 3
Mary 1
Mary 2
Mary 3
Paul 2
Larry 1
Larry 2
Larry 3
Joe 3

I need a query that returns only the people with a 2 or a 3, but don’t have any other numbers.

So the rows I would like to select would be –

Name IDNum
Paul 2
Joe 3

Thanks for help!

 

[vbaInet]

Just to clarify, you want those Names that have only 1 record AND their ID Num should be 2 OR 3?

 

[gswan]

I want the ones that have a 2 OR a 3. Some members may have both a 2 AND a 3, I would want the ones who have just one record where the ID is a 2 or a 3. I would not want to see the members who have both a 2 AND a 3.

 

[plog]

First you need a sub-query to get the total number of records a name has. Then you link that back to your main table and select only records that have 1 total record and who's IDnum is 2 or 3. This is the SQL that will do all that:

SELECT YourTableName.Name, YourTableName.IDnum
FROM YourTableName INNER JOIN [SELECT YourTableName.Name, Count(YourTableName.IDnum) AS NameTot
FROM YourTableName
GROUP BY YourTableName.Name]. AS ValidNames_sub ON YourTableName.Name = ValidNames_sub.Name
WHERE (((YourTableName.IDnum)=2 Or (YourTableName.IDnum)=3) AND ((ValidNames_sub.NameTot)=1))
ORDER BY YourTableName.IDnum;

 

[gswan]

That was it! It was a little more complicated than I thought it would be! Thanks so much for the help!

 

[gswan]

Ok, that wasn't exactly it. What I discovered is that some Members might have multiple entries with a 2 or a 3, so the table would look like this -

Name IDNum
John 1
John 2
John 3
Mary 1
Mary 2
Mary 3
Paul 2
Paul 2
Larry 1
Larry 2
Larry 3
Joe 3
Joe 3
Joe 3
Joe 3
Betty 2

So it doesn't work to count the rows and select the ones with just one entry. I've been trying to use a subquery to select the records and wrap it with a query "where not in", but I'm not having any luck getting that to work.

Again the desired result set from the table above would be all records where the member has a 2 or a 3, but no other number:
Paul 2
Paul 2
Joe 3
Joe 3
Joe 3
Betty 2

John, Mary and Larry should not appear in the result set since they have numbers other than 2 or 3.

 

[plog]

This requires a similar approach as the prior query, except how you use that sub-query is exactly different. Instead of holding who to include, the sub query will determine who to exclude. It will find all those people with an IDNUM other than 2 or 3 and then when linked back to the main table in a LEFT JOIN it will bounce them out, leaving just the ones you want.

SELECT YourTableName.IDNum, YourTableName.Name
FROM YourTableName LEFT JOIN (SELECT YourTableName.Name FROM YourTableName WHERE (((YourTableName.IDNum)<2 Or (YourTableName.IDNum)>3)) GROUP BY YourTableName.Name) AS Excluded ON YourTableName.Name = Excluded.Name
WHERE (((Excluded.Name) Is Null));

 

[gswan]

That works! Thanks for the help!