So I created a sql fiddle to explain my problem much clearer:
http://sqlfiddle.com/#!9/f35416
As you can see I have 3 tables and 1 of them links the 2 others.
table name: tags
---------------------
id | tag | value
---------------------
1 | color | green
2 | color | yellow
3 | color | red
4 | category | dress
5 | category | car
table name: product_tags_link
---------------------
product_id | tag_id
---------------------
1 | 1
1 | 5
2 | 1
3 | 2
3 | 5
4 | 4
5 | 4
5 | 1
table name: products
---------------------
id | name
---------------------
1 | green car
2 | yellow only
3 | yellow car
4 | dress only
5 | green dress
How can I make it so If I can get whatever product that have a "color" "green" and "category" "car"?
I tried doing:
select `ptl`.`product_id`
from `product_tags_link` as `ptl`
inner join `tags` on `tags`.`id` = `ptl`.`tag_id`
where ((`tags`.`tag` = "green") or (`tags`.`value` = "car"))
but it will return other product that is green OR car. changing it to and will not return anything as well.
I'm hoping to receive is product_id: 1 which have both color:green and category:car
Join all 3 tables, group by product and set the condition in the HAVING clause:
select p.id, p.name
from products p
inner join product_tags_link l on l.product_id = p.id
inner join tags t on t.id = l.tag_id
where (t.tag = 'category' and t.value = 'car')
or (t.tag = 'color' and t.value = 'green')
group by p.id, p.name
having count(distinct t.tag) = 2
Or:
select p.id, p.name
from products p
inner join product_tags_link l on l.product_id = p.id
inner join tags t on t.id = l.tag_id
where (t.tag, t.value) in (('category', 'car'), ('color', 'green'))
group by p.id, p.name
having count(distinct t.tag) = 2
See the demo.
Results:
> id | name
> -: | :---
> 1 | test