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.

Answers


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.


Need Your Help

Caching a column in a polymorphic relationship

ruby-on-rails caching polymorphic-associations

I have content management system application that uses a polymorphic tree table as the core of its arrangement. I've come into a problem where once the tree grows quite large, and because we have q...

sh.exe no such file or directory Python, GitBash

python git virtualenv virtualenvwrapper

I trying to setup virtualenvwrapper in GitBash (Windows 7), but get an error message "sh.exe"no such file or directory.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.