Sql: How to Select Only Groups That Do Not Contain a Certain Value

SQL: How do you select only groups that do not contain a certain value?

One way:

SELECT RESTAURANT_NO FROM restaurant WHERE RESTAURANT_NO NOT IN
(SELECT RESTAURANT_NO FROM restaurant WHERE RESTAURANT_LOCATION = 'City C')

SQL: Select groups that do not contain a certain value

Try this query...

SELECT employees.empid, Max(employees.firstname) AS FirstName 
FROM   certified 
       INNER JOIN employees ON employees.empid = certified.empid 
WHERE  certified.empid NOT IN (SELECT certified.empid 
                               FROM   certified 
                               INNER JOIN aircrafts ON aircrafts.aid = certified.aid 
                               WHERE  aircrafts.NAME LIKE 'BOEING%') 
GROUP  BY employees.empid

Demo: http://www.sqlfiddle.com/#!18/8f26d/27/0

Result

+-------+-----------+
| EMPID | FirstName |
+-------+-----------+
|     3 | TOM       |
|     5 | JACK      |
|     8 | MATT      |
+-------+-----------+

MYSQL display groups that do not contain certain value in one column

You can achieve your desired result using not exists clause. Try the below query it is checking every entity id for attribute id = 31.

SELECT * 
FROM customer_address_entity_varchar t1
WHERE 
NOT EXISTS (SELECT 1 FROM customer_address_entity_varchar t2 WHERE t1.entity_id = t2.entity_id
and attribute_id = 31  )

Selecting only such groups that contain certain value

Q: How does this work? I understand the "WHERE Department IN" part,
but then I'd expect a value, but instead another nested query is
included, which to me doesn't make much sense as I'm only beginner
with SQL.

The nested query returns values which are used to match against Department

2) By accident I was able to come up with another query that also
seems to work, but feels weird and I also don't understand its
workings.

 HAVING NOT SUM(FirstName = "Thomas") = 0

"Feels weird" because, well, it is. This is not a place for the SUM function.

EDIT: Why does this work?
The expression FirstName = "Thomas" gets evaluated as true or false (known as a Boolean expression). True numerically is equal to 1 and False converts to 0 (zero). By including SUM you then calculated the totals so really zero (still) means false and "not zero" is true. Then to make it weird(er) you included NOT which negated the whole thing and it becomes NOT TRUE = 0 or FALSE = FALSE (which is of course... TRUE)!!

EDIT: I think what could be more helpful to you is consideration of when to use WHERE and when to use HAVING (instead of the Boolean magic taking place).
From this answer:

WHERE clause introduces a condition on individual rows; HAVING clause introduces a condition on aggregations, i.e. results of selection where a single result, such as count, average, min, max, or sum, has been produced from multiple rows.

WHERE was appropriate for your example because first you want to "only return rows WHERE Department IN (100)" and then you want to "group those rows by Department" and get a COUNT of how many rows had been selected.

Search for records that do not contain a specific value

You need NOT EXISTS:

SELECT DISTINCT t1.CustomerId
FROM tablename t1
WHERE NOT EXISTS (SELECT 1 FROM tablename t2 WHERE t2.CustomerId = t1.CustomerId AND t2.SectionId = 1)

Or, with conditional aggregation:

SELECT CustomerId
FROM tablename
GROUP BY CustomerId
HAVING COUNT(CASE WHEN SectionId = 1 THEN 1 END) = 0

How to select rows from table where group does not contain value

You could use this:

SELECT *
FROM orclass o
WHERE Thedate = "2019-01-18"
    AND NOT EXISTS
    (
        SELECT 1
        FROM  orclass o1
        WHERE o1.Thedate = o.Thedate 
            AND o1.Thetime = o.Thetime 
            AND o1.lr3 = '0'
    )
ORDER BY Thetime;

SQL - how to select rows that do not contain a certain ID

SELECT whatever
FROM flag_counts
WHERE content_id NOT IN (
   SELECT content_id
   FROM flag_counts
   WHERE flag_id = 1
)

or via JOIN

SELECT DISTINCT c1.*
FROM flag_counts c1
LEFT JOIN flag_counts c2
 ON c1.content_id = c2.content_id and c2.flag_id = 1
WHERE c2.content_id IS NULL;

Select rows that do not contain a word from another table

contains means Oracle Text; cross join means Cartesian product (usually performance nightmare).

One option which avoids both of these is instr function (which checks existence of the constraint_word in text, but this time using inner join) and the minus set operator.

Something like this, using sample data you posted:

SQL> select * from text_table;

TEXT
---------------------------
word1.apple; word3, example
word1, apple, word2.car
word1 word2 orange word3
mushroomword1 word2 word3
word1 car
qwerty

6 rows selected.

SQL> select * From words_table;

CONSTRAI
--------
example
apple
orange
mushroom
car
qwerty

6 rows selected.

SQL>

As you said, initially query shouldn't return anything because all constraint_words exist in text:

SQL> select c.text
  2  from text_table c
  3  minus
  4  select b.text
  5  from words_table a join text_table b on instr(b.text, a.constraint_word) > 0;

no rows selected

Let's modify one of text rows:

SQL> update text_table set text = 'xxx' where text = 'qwerty';

1 row updated.

What's the result now?

SQL> select c.text
  2  from text_table c
  3  minus
  4  select b.text
  5  from words_table a join text_table b on instr(b.text, a.constraint_word) > 0;

TEXT
---------------------------
xxx

SQL>

Right; text we've just modified.

group by column not having specific value

The not exists operator seems to fit your need exactly:

SELECT DISTINCT Case_Id
FROM   [dbo].[caseRole] cr
WHERE  NOT EXISTS (SELECT *
                   FROM   [dbo].[caseRole] cr_inner
                   WHERE  cr_inner.Case_Id = cr.case_id 
                          AND cr_inner.RoleId = 4);