CODEWITHSUNDEEP
sqlselect
user3506778
user3506778

Reputation: 1

SQL - How to find a record that contains only 1 value in a field

I have a table that can look like this:

member       value
0001         1
0001         2
0001         3
0002         1 
0002         2
0003         1

essentially what I need to do is retrieve member 3 as they only have a record with a value of 1. Member 2 has records with values of 1 and 2 and member 3 has records with values 1, 2 and 3.

Please help!

Thanks

Rob

Upvotes: 0

Views: 5893

Answers (6)

Anthony Geoghegan
Anthony Geoghegan

Reputation: 11983

There are a lot of complex answers as of time of posting but the question can be answered quite simply using GROUP BY and HAVING:

select member
  from members_values
 group by member
having count(value) = 1

You haven't mentioned what RDBMS you're using but AIUI, the above code should be ANSI compatible; it also assumes your table is called something like members_values.

Upvotes: 0

Racil Hilan
Racil Hilan

Reputation: 25341

Try this:

SELECT member
FROM MyTable
GROUP BY member
HAVING COUNT(value) = 1

Upvotes: 0

gvee
gvee

Reputation: 17161

Sounds like you're looking to find the member that only exists once in that table...

The following query groups by member and proves the number of times that member appears in the table

SELECT member
     , Count(*) As number_of_apperances
FROM   your_table
GROUP
    BY member

Now we use the HAVING clause to limit this aggregate to show only those records who appear just once:

SELECT member
     , Count(*) As number_of_apperances
FROM   your_table
GROUP
    BY member
HAVING Count(*) = 1

Upvotes: 0

JonnyBoats
JonnyBoats

Reputation: 5187

This works in Transact-SQL

SELECT member, value
  FROM TableName
  WHERE value IN
  (SELECT value FROM TableName
   GROUP BY value
   HAVING COUNT(*) = 1)

If there is more than one member with a single entry it finds them all.

Upvotes: 1

jksemple
jksemple

Reputation: 257

select member
from (
select '0001' as member, 1 as value
union
select '0001' as member, 2 as value
union
select '0001' as member, 3 as value
union
select '0002' as member, 1 as value
union
select '0002' as member, 2 as value
union
select '0003' as member, 1 as value
) members
group by member
having count(*) = 1

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460028

I would use NOT EXISTS with a correlated subquery:

SELECT t.member, t.value
FROM dbo.TableName t
WHERE value = 1
AND NOT EXISTS
(
    SELECT 1 FROM dbo.TableName t2
    WHERE t2.value <> 1
    AND   t.member = t2.member
)

Demo

If you use MS Sql-Server NOT EXISTS is also the most efficient approach which has no issues on nullable columns. Worth reading: http://www.sqlperformance.com/2012/12/t-sql-queries/left-anti-semi-join

Upvotes: 0

Related Questions