CODEWITHSUNDEEP
sqlcountdistinct
Martyn Brearley
Martyn Brearley

Reputation: 59

SQL to find which members have only null entries

I'm racking my brain trying to work out what group by and/or distinct and count() formation help with this but have currently drawn a blank.

Consider the table:

PersonId | PlaceName
---------+----------
    1    |   NULL
    1    |   NULL
    2    |   NULL
    2    | Las Vegas
    3    | London
    4    |   NULL
    4    |   NULL
    4    |   NULL

I'm looking for people who haven't bothered to fill in "place names", so I want my output to look like this:

PersonId
--------
    1
    4

I'll actually be joining some other tables in to pull out the info on each "miscreant" person, but the crux of my problem is the one above.

Upvotes: 1

Views: 92

Answers (5)

Taryn
Taryn

Reputation: 247710

select distinct t1.id
from test t1
LEFT JOIN
(
  select id, count(name) nm
  from test 
  where name is not null
  group by id
) x
  on t1.id = x.id
where x.nm is null

See SQL Fiddle with Demo

Upvotes: 1

John Dewey
John Dewey

Reputation: 7093

SELECT DISTINCT PersonId
FROM MyTable t
WHERE NOT EXISTS(SELECT 1 FROM MyTable WHERE PersonId = t.PersonId AND PlaceName is not null)

Upvotes: 0

sybkar
sybkar

Reputation: 386

Use the following query:

SELECT PersonId
  FROM TheTable
 GROUP BY PersonId
 HAVING COUNT(PlaceName) = 0

The COUNT() aggregate function ignores NULLs, so the right result should be returned.

Upvotes: 2

David W
David W

Reputation: 10184

I would offer the following:

select distinct a.personid 
  from tablename a
  left outer join tablename b
    on a.personid=b.personid
   and b.placename is not null
 where b.personid is null
order by personid

Upvotes: 0

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171421

select PersonId
from MyTable
group by PersonId
having count(case when PlaceName is not null then 1 end) = 0

SQL Fiddle Example

Upvotes: 1

Related Questions