CODEWITHSUNDEEP
sqlsql-server-2008duplicates
PanosPlat
PanosPlat

Reputation: 958

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

Upvotes: 0

Views: 77

Answers (2)

varun kumar dutta
varun kumar dutta

Reputation: 202

Solution suggested by @jarlh is correct and preferable, alternatively you can use below SQL too:

SELECT  T.master_id
FROM    ( SELECT    master_id
          FROM      YourTable
          WHERE     value_id IS NOT NULL
          GROUP BY  master_id
        ) T
        INNER JOIN ( SELECT master_id
                     FROM   YourTable
                     WHERE  value_id IS NULL
                     GROUP BY master_id
                   ) AS R ON T.master_id = R.master_id

Upvotes: 1

jarlh
jarlh

Reputation: 44696

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

Upvotes: 2

Related Questions