Query with IN OPERATOR return only records that have all values reported

Question

I’m having a question of how to return records from a table have all the values reported in the operator IN using JOIN in another table.

I have a table of flags, colors and other making the relationship between the two (flags and colors);

flag table -> bandeira_id, nameBandeira

color table -> cor_id, CName

table flags -> id, flags, cor_id

Assuming the color 1 is Blue and the color 2 is White as I do to catch flags that have the colors Blue and White and not only White or only Blue.

I tried to use the operator IN but brought the flags that have the color Blue or White.

SELECT nomeBandeira FROM bandeira INNER JOIN cor  WHERE cor.cor_id IN (1,2)

Answer 1

What you want to do is called relational division. There are several ways to implement this in SQL; the one I used in this SQL Fiddle is, I think, the simplest, courtesy of Joe Celko:

SELECT
  bandeira_nome,
  COUNT(cor_id) AS total_cores
FROM
  bandeira
  INNER JOIN bandeira_cor USING (bandeira_id)
  INNER JOIN cor USING (cor_id)
WHERE cor_nome in ("Azul", "Branco")
GROUP BY bandeira_nome
HAVING total_cores = 2