How to select items on table based on 3 relations?
I have 3 tables:
Categories | id | name | 1 | Samsung | 2 | Apple Products | id | category_id | name | 1 | 1 | Galaxy S4 | 2 | 1 | Galaxy S3 | 3 | 1 | SHG-G600 | 4 | 3 | Lumia 920 Tags | id | product_id | name | type | 1 | 1 | smart-phone | phoneType | 2 | 2 | smart-phone | phoneType | 3 | 3 | normal-cell | phoneType | 4 | 1 | red | phoneColor
I'm trying to find a way to select all Samsung devices which have 'smart-phone' as 'phoneType' and 'red' as 'phoneColor'.
So this what I did until now:
SELECT * FROM `products` INNER JOIN `product_tag` ON `product_tag`.`product_id` = `products`.`id` INNER JOIN `tags` ON `tags`.`id` = `products`.`id` WHERE ( `tags`.`type` = 'phoneType' AND `tags`.`name` = 'smart-phone' ) OR ( `tags`.`type` = 'phoneColor' AND `tags`.`name` = 'red' ) )
This did not work as is (without selecting category).
I also didn't know how to join categories and add where categories.id = 1.
You can do this by putting the logic in the having clause. For your example code:
SELECT p.* FROM `products` p join `product_tag` pt ON pt.`product_id` = p.`id` join `tags` t ON t.`id` = p.`id` group by p.id having sum(t.`type` = 'caseMaterial' AND t.name = 'leather') > 0 and sum(t.`type` = 'caseFor' AND t.`name` = 'iphone-5') > 0;
However, I'm not quite sure how this relates to the tables at the beginning of the question. Your code sample and data layout are not consistent.
I extended the solution of @Gordon Linoff by adding the category join.
SELECT p.* FROM `products` p join `categories` c ON c.`id` = p.`category_id` join `product_tag` pt ON pt.`product_id` = p.`id` join `tags` t ON t.`id` = pt.`tag_id` where c.id = 1 group by p.id having sum(t.`type` = 'phoneType' AND t.name = 'smart-phone') > 0 and sum(t.`type` = 'phoneColor' AND t.`name` = 'red') > 0
This is working now. Thanks to @Gordon Linoff.