Paste this into a new question or an answer at dba.stackexchange.com:
<!-- --> > CREATE TABLE ItemKeyValues > ( > ItemID int(11) unsigned NOT NULL, > KeyID mediumint(7) unsigned, > ValueID mediumint(7) unsigned > ); > INSERT INTO ItemKeyValues (ItemID, KeyID, ValueID) VALUES > (1,1,1), > (1,2,2), > (1,3,3), > (1,4,4), > (2,1,1), > (2,2,2), > (2,3,4), > (2,5,5); > SELECT * FROM ItemKeyValues; > CREATE TABLE Pairs > ( > PairID int(11) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY, > Key1 mediumint(7) unsigned, > Value1 mediumint(7) unsigned, > Key2 mediumint(7) unsigned, > Value2 mediumint(7) unsigned > ); > > CREATE TABLE ItemPairs > ( > PairID int(11) unsigned NOT NULL, > ItemID int(11) unsigned NOT NULL > ); > > <pre> > ✓ > > ✓ > > ItemID | KeyID | ValueID > -----: | ----: | ------: > 1 | 1 | 1 > 1 | 2 | 2 > 1 | 3 | 3 > 1 | 4 | 4 > 2 | 1 | 1 > 2 | 2 | 2 > 2 | 3 | 4 > 2 | 5 | 5 > > ✓ > > ✓ > </pre> <!-- --> > INSERT INTO Pairs > SELECT DISTINCT NULL, t1.KeyID, t1.ValueID, t2.KeyID, t2.ValueID > FROM ItemKeyValues t1 > JOIN ItemKeyValues t2 USING (ItemID) > WHERE t1.KeyID < t2.KeyID; > SELECT * FROM Pairs; > > <pre> > ✓ > > PairID | Key1 | Value1 | Key2 | Value2 > -----: | ---: | -----: | ---: | -----: > 1 | 1 | 1 | 2 | 2 > 2 | 2 | 2 | 3 | 3 > 3 | 1 | 1 | 3 | 3 > 4 | 3 | 3 | 4 | 4 > 5 | 2 | 2 | 4 | 4 > 6 | 1 | 1 | 4 | 4 > 7 | 2 | 2 | 3 | 4 > 8 | 1 | 1 | 3 | 4 > 9 | 3 | 4 | 5 | 5 > 10 | 2 | 2 | 5 | 5 > 11 | 1 | 1 | 5 | 5 > </pre> <!-- --> > INSERT INTO ItemPairs > SELECT t1.PairID, t2.ItemID > FROM Pairs t1 > JOIN ItemKeyValues t2 ON t1.Key1 = t2.KeyID > AND t1.Value1 = t2.ValueID > JOIN ItemKeyValues t3 ON t1.Key2 = t3.KeyID > AND t1.Value2 = t3.ValueID > AND t2.ItemID = t3.ItemID; > SELECT * FROM ItemPairs; > > <pre> > ✓ > > PairID | ItemID > -----: | -----: > 1 | 2 > 1 | 1 > 2 | 1 > 3 | 1 > 4 | 1 > 5 | 1 > 6 | 1 > 7 | 2 > 8 | 2 > 9 | 2 > 10 | 2 > 11 | 2 > </pre> *db<>fiddle [here](https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=baea6705a124652173003151e8c4bf58)*
back to fiddle