sql filter on multiple columns

Question

Tree:

id name
1  apple
2  aspen

TreeAttributeCategorie:

id categorie
1  leafColour
2  trunkColour

TreeAttributes:

id tree_id attribute tree_attribute_categorie_id
1  1       brown     1
2  1       brown     2
3  2       green     1
4  2       brown     2

How would the sql statement look to filter like following(attributes and tree_attribute_categorie_id):

[[brown and 1] OR [red and 1]] AND [[brown and 2] OR [green and 2]] = return apple tree
[[brown and 1] OR [green and 1]] AND [[brown and 2] OR [green and 2]] = returns both trees

Answer 1

You can use group by and having:

select t.*
from tree t
inner join treeattributes ta on ta.tree_id = t.id
group by t.id
having max(ta.tree_attribute_categorie_id = 1 and ta.attribute in ('brown', 'red')) = 1
   and max(ta.tree_attribute_categorie_id = 2 and ta.attribute in ('brown', 'green')) = 1

This matches the first filter specification in your question. The having clause for second spec would look like:

having max(ta.tree_attribute_categorie_id = 1 and ta.attribute in ('brown', 'green')) = 1
   and max(ta.tree_attribute_categorie_id = 2 and ta.attribute in ('brown', 'green')) = 1

If you wanted to filter by attribute name rather than by attribute id, you would add one more join, and adapt the having clause:

select t.*
from tree t
inner join treeattributes ta on ta.tree_id = t.id
inner join treeattributecategori tag on tag.id = ta.tree_attribute_categorie_i
group by t.id
having max(tag.categorie = 'leafColour' and ta.attribute in ('brown', 'red')) = 1
   and max(tag.categorie = 'trunkColour' and ta.attribute in ('brown', 'green')) = 1