March 20, 2012
MYSQL filter table by related options table with grouped records
Question by Konstantin
I have three tables:
items:
id name
4 Item Blue, L
2 Item Blue, S
6 Item Green
8 Item L
9 Item Red, Blue, Green, S, M, L
5 Item Red, L
3 Item Red, M
1 Item Red, S
7 Item S
options:
id group name
1 1 red
2 1 blue
3 1 green
4 2 s
5 2 m
6 2 l
items_has_options:
item_id options_id
1 1
1 4
2 2
2 4
3 1
3 5
4 2
4 6
5 1
5 6
6 3
7 4
8 6
9 1
9 2
9 3
9 4
9 5
9 6
How can i select items which have options: (red or blue or green) and (s)? I need to choose items which have some options from different groups. Options may have many groups, not only two. Result must be:
2 Item Blue, S
9 Item Red, Blue, Green, S, M, L
1 Item Red, S
Answer by barsju
Not sure this will work but it might get you started..
SELECT DISTINCT i.id, i.name FROM items i
INNER JOIN items_has_options ihs1 ON
i.id = ihs1.item_id
INNER JOIN items_has_options ihs2 ON
i.id = ihs2.item_id
INNER JOIN options o1 ON
ihs1.options = o1.id AND (o1.name = "red" OR o1.name = "blue" OR o1.name="green")
INNER JOIN options o2 ON
ihs2.options = o2.id AND o2.name = "s"
The idea is that you join with the options table twice, once for color and once for size.
You need the distinct since you will get duplicate rows from o1…
Answer by Starx
I haven’t tested this but, should work
SELECT count(iid), i.name FROM items i
INNER JOIN items_has_options ihs ON
i.id = ihs.item_id
INNER JOIN options oON
ihs.options = o.id
WHERE i.name ='Item Blue, S'
