clear markdown help best fiddles feedback dbanow.uk
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2591678 fiddles created (45711 in the last week).

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 );
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
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
 hidden batch(es)


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;
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
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
 hidden batch(es)


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;
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
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
 hidden batch(es)