I have a select query to build and I can't get it work propery. I was hopeing for a few sudggestions from more advanced MySQL developers. So my tables are:
CREATE TABLE IF NOT EXISTS `gv` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`option_id` int(11) NOT NULL,
`group_id` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;
INSERT INTO `gv` (`id`, `option_id`, `group_id`) VALUES
(1, 1, 1),
(2, 2, 2),
(3, 3, 2),
(4, 4, 3),
(5, 5, 4),
(6, 6, 4);
CREATE TABLE IF NOT EXISTS `igv` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`item_id` int(11) NOT NULL,
`gv_id` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;
INSERT INTO `igv` (`id`, `item_id`, `gv_id`) VALUES
(1, 1, 1),
(2, 1, 3),
(3, 2, 1),
(4, 2, 2),
(6, 3, 5),
(7, 4, 2),
(8, 2, 6);
CREATE TABLE IF NOT EXISTS `items` (
`item_id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;
INSERT INTO `items` (`item_id`) VALUES
(1),
(2),
(3),
(4),
(5);
Now I will explain what these tables are doing:
gv comes from group_values. A group is like a property for an item. Ex: Color.
igv comes from item group values. An item is the main entity in my project, like a product.
items comes from items :) I have simplified for this example to only one column, item_id.
What I need to query:
Well, in my app I have a selected item ( my_item ) ( with 0 or more group values attached ). I need all the items that have exactly the same group_values defined, or no values specified for my_item groups. In php I can extract all the attached groups as an array with ids, also all the group_values. But I found no way to select what I need to from this database. I will appreciate very much any input provided into this massive time-consumming select.
Thanks!
A short sketched-up example. On the left we have the item that is matching all the right items ( separated with comma ).
OK, so to start with we need to find all items with a group matching another item:
SELECT igv.item_id, sigv.item_id FROM igv
INNER JOIN gv ON igv.gv_id = gv.id
INNER JOIN gv sgv ON gv.group_id = sgv.group_id
INNER JOIN igv sigv ON sigv.gv_id = sgv.id
WHERE igv.item_id = items.item_id
AND sigv.item_id = similar.item_id
We then are only interested when the option does not match:
SELECT igv.item_id, sigv.item_id dissimilar FROM igv
INNER JOIN gv ON igv.gv_id = gv.id
INNER JOIN gv sgv ON gv.group_id = sgv.group_id
INNER JOIN igv sigv ON sigv.gv_id = sgv.id
WHERE igv.item_id = items.item_id
AND sigv.item_id = similar.item_id
AND gv.option_id != sgv.option_id
Then we are only interested in the items that are not selected above. This can be done using either NOT EXISTS
or a LEFT JOIN
. Here's how to do it with NOT EXISTS
:
SELECT items.item_id, similar.item_id similar_id
FROM items similar
INNER JOIN items
WHERE items.item_id != similar.item_id
AND NOT EXISTS (
SELECT igv.item_id, sigv.item_id dissimilar FROM igv
INNER JOIN gv ON igv.gv_id = gv.id
INNER JOIN gv sgv ON gv.group_id = sgv.group_id
INNER JOIN igv sigv ON sigv.gv_id = sgv.id
WHERE sigv.item_id = similar.item_id
AND igv.item_id = items.item_id
AND gv.option_id != sgv.option_id
)
Result:
ITEM_ID SIMILAR_ID
1 3
1 5
2 4
2 5
3 1
3 4
3 5
4 2
4 3
4 5
5 1
5 2
5 3
5 4