Exclude results with join conditions

Question

I'm trying to make an sql request with join exclusion. Explains:

Table element

id # name #
 1  Sea
 2  tree

Table colour

id # name #
 1    green
 2    blue
 3    brown

Table relation

element_id # colour_id
     1           2
     2           1
     2           3

I have my working request for "get elements for one of these colours". Exemple with green and blue:

SELECT element.name, colour.name FROM element
LEFT JOIN relation 
  ON (element.id = relation.element_id)
LEFT JOIN colour
  ON (colour.id = relation.colour_id)
WHERE (relation.colour_id = 1 OR relation.colour_id = 2)

I would like make request for "get elements where they have a relation with all listed colors". Where for green and brown it returns tree.

I've tried to change the 'OR' to 'AND' but request return 0 results :/

Answer 1

General way to solve this problem is to filter values and count how many times they appear in result. If equal, all elements are found.

select element_id
from relation
where colour_id in (1, 2)
group by element_id
having count (distinct colour_id) = 2

Having this table one might join it to original tables to produce full column set:

SELECT element.name, colour.name 
  FROM relation 
  INNER JOIN
  (
    select element_id
      from relation
     where colour_id in (1, 2)
     group by element_id
    having count (distinct colour_id) = 2
  ) matches
    ON relation.element_id = matches.element_id
  INNER JOIN element
     ON element.id = relation.element_id
  INNER JOIN colour
     ON colour.id = relation.colour_id