MySQL: Query all records that have a given set of relations to another table

Question

Yeah, kinda dumb title, but I found it hard to describe my problem. I have the following tables:

properties

id | name
1  | color
2  | material

options

id | property_id | name
1  | 1           | yellow
2  | 1           | blue
3  | 2           | wood
4  | 2           | stone

substances

id | name
1  | orange juice
2  | cheese

relations

id | substance_id | option_id
1  | 2            | 1
2  | 2            | 3
3  | 1            | 1

Now, I have a list of options and want to know which substances are related to all those options. (For example, which substances are yellow and made of wood?) Is this possible with one query?

I'm trying to do this in Rails.

Answer 1

Just out of my head, you can try:

SELECT DISTINCT s.name FROM substances s, relation r 
WHERE r.substance_id = s.id 
      AND r.option_id IN ( 1, 3) 

Answer 2

(SELECT s.name FROM substances s, relations r, options o
WHERE r.substance_id = s.id and r.option_id = o.id and o.name='yellow')
INTERSECT
(SELECT s.name FROM substances s, relations r, options o
WHERE r.substance_id = s.id and r.option_id = o.id and o.name='wood')

Or

SELECT s.name FROM substances s
WHERE exists(SELECT * from relations r, options o 
      WHERE r.substance_id = s.id and r.option_id = o.id and o.name='yellow')
AND   exists(SELECT * from relations r, options o 
      WHERE r.substance_id = s.id and r.option_id = o.id and o.name='wood')

Having a single options table with the property_id to tell apart different kinds of options is not such a good idea and really makes this much harder. I'd suggest breaking out the different kinds of options into different tables

MATERIALS(id,name)
COLORS(id,name)

and using separate relations for each type of table. In this case, you wouldn't need separate tables for each relation, since it appears that it's a many (substance) to one (color) relation.

SUBSTANCES(id, name, material_id, color_id)

Then your query is much simpler

SELECT s.name FROM substances s, materials m, colors c
WHERE s.color_id = c.id AND m.material_id = m.id
AND m.name = 'wood'
AND c.name = 'yellow'

ActiveRecord should easily be able to handle this last query much more easily than the first two.

Answer 3

SELECT s.name
FROM   substances AS s
       LEFT JOIN relations AS r1
         ON s.id = r1.substance_id
       INNER JOIN relations AS r2
         ON r1.substance_id = r2.substance_id
            AND r1.option_id < r2.option_id
       LEFT JOIN options AS o1
         ON o1.id = r1.option_id
       LEFT JOIN options AS o2
         ON o2.id = r2.option_id
WHERE  o1.name = 'yellow'
       AND o2.name = 'wood'  

I'm not very knowledgable about optimizing SQL for performance. You may want to benchmark the above versus this (below) and well as the other solutions posted here.

SELECT s.name
FROM   substances AS s
       LEFT JOIN relations AS r1
         ON s.id = r1.substance_id
       INNER JOIN relations AS r2
         ON r1.substance_id = r2.substance_id
            AND r1.option_id < r2.option_id
       LEFT JOIN options AS o1
         ON o1.id = r1.option_id
     AND o1.name = 'wood'
       LEFT JOIN options AS o2
         ON o2.id = r2.option_id
     AND o2.name = 'yellow'