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

Asked

Viewed 360 times

4

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)
  • Which DBMS are you using?

  • I think it’s independent of DBMS...

  • If it is using SQL Server I can resolve it using window functions. If you are using Mysql I cannot do this.

1 answer

3


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
  • ctgPi, that’s what I wanted! = ) Thank you!

Browser other questions tagged

You are not signed in. Login or sign up in order to post.