CODEWITHSUNDEEP
sqlsql-server
Lasse Solberg
Lasse Solberg

Reputation: 23

sql query find missing value from duplicates

I have a table like this:

|__ID__|__Key__|__Value__|
|  1   |  AA   |   2     |
|  2   |  AA   |   2     |
|  2   |  BB   |   2     |
|  2   |  CC   |   2     |
|  3   |  BB   |   2     |
|  3   |  AA   |   2     |

I'm trying to build a query that checks which IDs are missing the Key BB.

So in the example above I would en up with the result 

|__ID__|
|  1   |

I've tried searching for similar questions here on StackOVerflow and other sites but I always end up with the result listing all ID rows that does not have the key BB. In the example above I'd get 1,2,2,3.

Upvotes: 2

Views: 407

Answers (3)

Mohammad
Mohammad

Reputation: 1990

You can accomplish that by many ways. One of them is to select IDs that have the key BB which is easy then subtracting this from the full set of IDs:

SELECT DISTINCT ID FROM Table1 WHERE ID NOT IN
(SELECT ID FROM Table1 WHERE [Key] = 'BB')

Here is SQLFiddle showing the above solution: http://www.sqlfiddle.com/#!18/47db9/6

Other ways to solve can be found in this SO question: SQL: How do you select only groups that do not contain a certain value?

To search for what you need to try form your question like: "how to select groups that don't contain a certain element" because in principle you want to group elements by id and retrieve only certain groups even though we didn't use a GROUP BY statement to solve it

Upvotes: 1

Thom A
Thom A

Reputation: 95606

Like I said in the comments, I would personally use HAVING:

SELECT ID
FROM YourTable 
GROUP BY ID
HAVING COUNT(CASE WHEN [Key] = 'BB' THEN 1 END) = 0;

DB<>fiddle for the person that believes this doesn't work.

Upvotes: 0

iamdave
iamdave

Reputation: 12243

You need to filter your dataset based on the values that aren't in your dataset, which is usually best done using a not exists:

declare @t table (id int,keys varchar(50),value int);
insert into @t values (1,'AA',2),(2,'AA',2),(2,'BB',2),(3,'AA',2),(3,'BB',2);

select t1.id
from @t as t1
where not exists(select id
                 from @t as t2
                 where keys = 'BB'
                    and t1.id = t2.id
                );

Output

+----+
| id |
+----+
|  1 |
+----+

Upvotes: 2

Related Questions