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

Question

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

Navigation

#1 by (3 votes)

4

I'm having a question about how to return records from a table that have all the values entered in the IN operator using JOIN in another table.

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

flag table - > bandeira_id, NomeBandeira

table color - > cor_id, colorName

table bandeira_cor - > id, flag_id, cor_id

Assuming that color 1 is Blue and color 2 is White, how do I get flags that have Blue and White colors and not just White or Blue only.

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

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

sql

asked by anonymous 16.06.2015 / 19:07

1 answer

JAVA Connection with BANK Doubt how to draw several different pictures

score 3 · Accepted Answer

What you want to do if you call it 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