CODEWITHSUNDEEP
mysqlrelational-databasepermutation
HMcG
HMcG

Reputation: 7

MySQL permutations of single list

Bit of a database newbie here. I have a table list this:

author_id   book_id(PK)   book_title   other_book_info
======================================================
1           1             Grass        etc.
1           2             Fire         etc.
2           3             Green        etc.
2           4             Red          etc.

I'm interested in matching all books from Author1 with all books from Author2 with some correlation called book_similarity (outside the scope of the question). What's the best practice way of creating a table of all permutations like this?:

sim_id(PK)   book_id1    book_id2     book_similarity
=====================================================
1            1           3            High
2            1           4            Low
3            2           3            Low
4            2           4            High

Book similarity is commutative (ie. matching 1 vs. 3 is the same as 3 vs. 1) so I only need the permutations of book titles, not the combinations.

However, I suspect that it would be best practice to create a longer table with all combinations that has 16 rows so that if I'm looking for a particular book (say #4), that I only need to look in book_id1 rather than book_id1 and book_id2. What's the consensus?

Upvotes: 0

Views: 177

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

You want to do a cross join and select the particular authors you want:

select (@rn := @rn + 1) as id, b1.book_id as book_id1, b2.book_id as book_id2,
       YOURSIMILARITYFUNCTIONHERE
from books b1 cross join
     books b2 cross join
     (select @rn := 0) vars
where b1.author_id = 1 and b2.author_id = 2;

Without the where, this will choose all pairwise combinations.

Upvotes: 1

Related Questions