How to retrieve data from two MySQL tables with multiple associations

I got these two tables where one table is having multiple foreign keys to the second table.

Table rankings

+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| id             | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| search_text    | varchar(255) | YES  | MUL | NULL    |                |
| first_item_id  | bigint(20)   | YES  | MUL | NULL    |                |
| second_item_id | bigint(20)   | YES  | MUL | NULL    |                |
| third_item_id  | bigint(20)   | YES  | MUL | NULL    |                |
| forth_item_id  | bigint(20)   | YES  | MUL | NULL    |                |
+----------------+--------------+------+-----+---------+----------------+

Table item

+---------------------------+--------------+------+-----+---------+----------------+
| Field                     | Type         | Null | Key | Default | Extra          |
+---------------------------+--------------+------+-----+---------+----------------+
| id                        | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| item_code                 | varchar(255) | YES  | MUL | NULL    |                |
+----------------+--------------+------+-----+---------+---------------------------+

One ranking record may have multiple association to item table using first_item_id, second_item_id, third_item_id or forth_item_id fields. I want to retrieve ranking records with the corresponding item_code instead of the item.id. What would be the most efficient way to do this if i have a big number of data?

PS: There are 10 associations to the item.id as first_item_id … tenth_item_id. Im using Rails ActiveRecord ORM. Any workaround with that also fine.

Sample data ranking

SELECT id,search_text,first_item_id as first,second_item_id as second,third_item_id as third,forth_item_id as forth from rankings limit 10;

+----+-------------+-------+--------+-------+-------+
| id | search_text | first | second | third | forth |
+----+-------------+-------+--------+-------+-------+
|  1 | test 1      |     1 |      2 |     3 |     4 |
|  2 | test 2      |     1 |      2 |     3 |     4 |
|  3 | test 3      |     1 |      2 |     3 |     4 |
|  4 | test 4      |     1 |      2 |     3 |     4 |
+----+-------------+-------+--------+-------+-------+

Sample item data

SELECT id,item_code from items limit 5;

+--------+------------+
| id     | item_code  |
+--------+------------+
|      1 | 125659     |
|      2 | 125660     |
|      3 | 125661     |
|      4 | 125662     |
+--------+------------+

Expected data

+----+-------------+-------+--------+-------+-------+
| id | search_text | first | second | third | forth |
+----+-------------+-------+--------+-------+-------+
|  1 | test 1      | 125659| 125660 | 125661| 125662|
|  2 | test 2      | 125659| 125660 | 125661| 125662|
|  3 | test 3      | 125659| 125660 | 125661| 125662|
|  4 | test 4      | 125659| 125660 | 125661| 125662|
+----+-------------+-------+--------+-------+-------+

Answer

Joining the table multiple times (even many, many times) should not be a problem, as you are joining on the primary key, i.e. you have an index that will be used.

select 
  r.id,
  r.search_text,
  i1.item_code as item_code_1,
  i2.item_code as item_code_2,
  i3.item_code as item_code_3,
  i4.item_code as item_code_4
from rankings r
left join item i1 on i1.id = r.first_item_id
left join item i2 on i2.id = r.second_item_id
left join item i3 on i3.id = r.third_item_id
left join item i4 on i4.id = r.forth_item_id
order by r.id;

I am using outer joins here, because all your item columns are nullable.

Advertisement

Answer