PanosPlat PanosPlat - 6 months ago 11
SQL Question

SQL Find duplicates

I have a table (mytable) that I need to find the master_id that has both null and not null values in the value_id column

master_id value_id
1 1
1 2
1 1
2 1
2 null
3 null
3 null


In the above example I need to fetch the master_id = 2 because it contains both null and not null value_id. master_id = 1 is OK

How can I achieve this? I am using SQL Server 2005/2008R2

Thanx

Answer

Use GROUP BY/HAVING. count(*) will count all rows for a master_id, count(value_id) will count the rows where value_id is not null.

select master_id
from mytable
group by master_id
having count(*) <> count(value_id) and count(value_id) > 0

Execution example:

SQL>create table mytable (master_id int, value_id int);
SQL>insert into mytable values (1,1);
SQL>insert into mytable values (1,2);
SQL>insert into mytable values (1,1);
SQL>insert into mytable values (2,1);
SQL>insert into mytable values (2,null);
SQL>insert into mytable values (3,null);
SQL>insert into mytable values (3,null);
SQL>select master_id, count(*) totcnt, count(value_id) nonnullcnt
SQL&from mytable
SQL&group by master_id;
  master_id               totcnt           nonnullcnt
=========== ==================== ====================
          1                    3                    3
          2                    2                    1
          3                    2                    0

                  3 rows found

SQL>select master_id
SQL&from mytable
SQL&group by master_id
SQL&having count(*) <> count(value_id) and count(value_id) > 0;
  master_id
===========
          2

                  1 row found

Derived table version:

select master_id
from
(
    select master_id,
           count(case when value_id is not null then 1 end) as nn,
           count(case when value_id is null then 1 end) as n
    from mytable
    group by master_id
)
where nn > 0 and n > 0

Executes as:

SQL>select master_id
SQL&from
SQL&(
SQL&    select master_id,
SQL&           count(case when value_id is not null then 1 end) as nn,
SQL&           count(case when value_id is null then 1 end) as n
SQL&    from mytable
SQL&    group by master_id
SQL&)
SQL&where nn > 0 and n > 0;
  master_id
===========
          2

                  1 row found
Comments