Menu
I am trying to write a query that will return what hosts are missing a piece of software:
Host Software
A Title1
A Title2
A Title3
B Title1
B Title3
C Title4
C Title3
How to query for which hosts are missing Title2 (should be B and C)? I've tried GROUP BY, HAVING and subquery using COUNT but I don't seem to have the right idea.
279
SQL not like statement syntax will be like below. SELECT column FROM table_name WHERE column NOT LIKE pattern; UPDATE table_name SET column=value WHERE column NOT LIKE pattern; DELETE FROM table_name WHERE column NOT LIKE pattern; As an example, let's say we want the list of customer names that don't start with 'A'.
The NOT LIKE operator in SQL is used on a column which is of type varchar . Usually, it is used with % which is used to represent any string value, including the null character \0 . The string we pass on to this operator is not case-sensitive.
If != and <> both are the same, which one should be used in SQL queries? Here is the answer – You can use either != or <> both in your queries as both technically same but I prefer to use <> as that is SQL-92 standard.
The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. There are two wildcards often used in conjunction with the LIKE operator: The percent sign (%) represents zero, one, or multiple characters. The underscore sign (_) represents one, single character.
I think a simpler way of doing this is:
select software
from HostSoftware hs
group by software
having max(case when software = 'Title2' then 1 else 0 end) = 0
This does not require a correlated subquery. And, it should result in better execution plans on most databases.
152
SELECT Host FROM HostSoftware
WHERE NOT EXISTS (
SELECT * FROM HostSoftware AS InnerSoftware
WHERE InnerSoftware.Host = HostSoftware.Host AND InnerSoftware.Software ='Title2'
)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
