[SOLVED] SQL SELECT all datasets where none of them match a criteria

SQL SELECT all datasets where none of them match a criteria

I wonder if I can request following criteria from a Log Parser database. (Yes, it's shitty to not have two databases for this but that's what it is.)

Scheme:

+-------------------------------------+
|  RegKey   |  Hostname | WinVersion  |
+-----------+-----------+-------------+
|  Regkey1  |   PC1     |    1909     |
|  Regkey2  |   PC1     |    1909     |
|  Regkey3  |   PC1     |    1909     |
|  Regkey1  |   PC2     |    1803     |
|  Regkey1  |   PC3     |    1909     |
|  Regkey2  |   PC3     |    1909     |
+-------------------------------------+

Hostname and WinVersion do fit together in each dataset but each regkey has it's own dataset. What I'm looking for is a statement to list or count for example ALL Hostnames WHERE NONE of all datasets HAS (RegKey LIKE RegKey3). So that it results in a list of all Hostnames where RegKey3 is missing.

When I use WHERE and GROUP BY, of course I get all Hostnames because only the RegKey3 datasets are filtered. But is this even possible? I tried with HAVING but this seems to be only valid with counting or number comparison.

Thanks!

Nico

Solution

You can use:

select hostname
from t
group by hostname
having sum(case when RegKey LIKE RegKey3 then 1 else 0 end) = 0;

You can try this alternative:

select distinct hostname
from t
where not exists (select 1 from t t2 where t2.hostname = t.hostname and t2.RegKey like RegKey3);