sql query find missing value from duplicates

Question

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.

What have you tried so far? Have you had a look at `NOT EXISTS`, or `HAVING` with a conditional aggregate? (Personally, as you only need the `ID`, I would go with `HAVING`.) — Thom A, Jun 07 '19 at 09:07
I have tried both NOT EXISTS and HAVING, but I can't seem to make it work. I know I need more practice in building queries :D And you are correct. I only need the ID for the result. — Lasse Solberg, Jun 07 '19 at 09:17

score 2 · Answer 1 · answered Jun 07 '19 at 09:16

2

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 |
+----+

answered Jun 07 '19 at 09:16

iamdave

12,023
3
24
53

The problem with this is that there are a lot of different Key values. So what I have tried to create is a query where it groups the same number IDs and check if Key BB does not exist in that array. – Lasse Solberg Jun 07 '19 at 09:20
@LasseSolberg Sorry, but you comment does not make sense to me... Do you mean you are filtering on many different Key values and not just `BB`? – iamdave Jun 07 '19 at 09:22
@iamdave try your solution with this dataset `values (1,'BB',2),(2,'BB',2),(4,'BB',2),(1,'AA',2), (2,'AA',2);`, it will return nothing – Vasily Jun 07 '19 at 09:24
@VasilyIvoyzha My query returns no result, as all `id` values have a `BB` `key`? – iamdave Jun 07 '19 at 09:26
@LasseSolberg Did you actually try my query? I guarantee this will return the correct results. – iamdave Jun 07 '19 at 09:27
I think that Vasily Ivoyzha is confused on the OP's requirement @iamdave. UIa gree this would work exactly as the OP has asked. – Thom A Jun 07 '19 at 09:28
@iamdave I mean that there are close to a hundred different Key values in the table. – Lasse Solberg Jun 07 '19 at 09:31
@LasseSolberg That is not an issue. Try my query on your data and see how it goes. – iamdave Jun 07 '19 at 09:55

score 1 · Answer 2 · answered Jun 07 '19 at 09:25

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

score 0 · Accepted Answer · answered Jun 07 '19 at 09:18

0

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.

answered Jun 07 '19 at 09:18

Thom A

88,727
11
45
75

Works like a charm! thank you :) I didnt downvote, I try to upvote but get this message "Thanks for the feedback! Votes cast by those with less than 15 reputation are recorded, but do not change the publicly displayed post score." – Lasse Solberg Jun 07 '19 at 09:24
You're welcome @LasseSolberg . I just wish I knew how I could "improve" the question further to please the downvoter. If you don't understand the answer please do ask, however, I *hope* this one is quite self explanatory, :) – Thom A Jun 07 '19 at 09:25
@Larnu try your solution with this dataset `values (1,'BB',2),(2,'BB',2),(4,'BB',2),(1,'AA',2), (2,'AA',2);`, it will return nothing – Vasily Jun 07 '19 at 09:26
From the description the OP gave, for that dataset it *should* return nothing. IDs 1, 2 and 4 **all** have the a `key` with the value `BB`. The OP has even stated that this answers their question. I don't think you've understood the OP's request. – Thom A Jun 07 '19 at 09:27
@VasilyIvoyzha your dataset has no ID `3`... A row can't be returned for a value that does not exist. The OP isn't asking to identify "missing" ID values, they are looking to return IDs that don't have the value `'BB'` for `key` – Thom A Jun 07 '19 at 09:28
Change your data set to `values (1,'BB',2),(2,'BB',2),(4,'BB',2),(1,'AA',2), (2,'AA',2), (3,'AA',4)`; and you'll have `3` returned, @VasilyIvoyzha. – Thom A Jun 07 '19 at 09:30

sql query find missing value from duplicates

3 Answers3

Output