EverSQL Database Performance Knowledge Base
Joining Multiple Tables Based on Two Cross Reference Tables
Database type:
I have a database (MySQL 5.1) that uses a cross reference table (local_ref in the example below) to get an numerical ID for a value. I've introduced another cross reference table (foreign_ref, below) to reference these numerical IDs to an index in another database. Normally that's not a complicated join, however, I have multiple columns that use the keys from the cross referenced table (val1 and val2, below).
E.g.:
mysql> select * from foo;
+-----+------+------+
| id | val1 | val2 |
+-----+------+------+
| 100 | A | B |
| 200 | A | D |
| 300 | B | C |
+-----+------+------+
mysql> select * from local_ref;
+----+-------+
| id | value |
+----+-------+
| 1 | A |
| 2 | B |
| 3 | C |
| 4 | D |
| 5 | E |
+----+-------+
mysql> select * from foreign_ref;
+----------+------------+
| local_id | foreign_id |
+----------+------------+
| 1 | 10 |
| 2 | 20 |
| 3 | 30 |
| 4 | 40 |
+----------+------------+
What I need is the following:
+-----+---------+---------+
| id | val1_id | val2_id |
+-----+---------+---------+
| 100 | 10 | 20 |
| 200 | 10 | 40 |
| 300 | 20 | 30 |
+-----+---------+---------+
Knowing that the original table isn't normalized as it should be, I've achieved the results the following two ways:
Aliasing both cross-reference tables twice:
SELECT
FOO.id, F_R1.foreign_id, F_R2.foreign_id
FROM FOO
JOIN
Local_Ref as L_R1 ON (FOO.val1 = L_R1.value)
JOIN
Local_Ref as L_R2 ON (FOO.val2 = L_R2.value)
JOIN
Foreign_Ref as F_R1 ON (L_R1.id = F_R1.local_id)
JOIN
Foreign_Ref as F_R2 ON (L_R2.id = F_R2.local_id)
Joining the cross-reference tables twice and aliasing each join.
SELECT
FOO.id, joint1.foreign_id, joint2.foreign_id
FROM
FOO
JOIN
(
SELECT * FROM Local_Ref JOIN Foreign_Ref ON Local_Ref.id = Foreign_Ref.local_id
) as joint1
ON FOO.val1 = joint1.value
JOIN
(
SELECT * FROM Local_Ref JOIN Foreign_Ref ON Local_Ref.id = Foreign_Ref.local_id
) as joint2
ON FOO.val2 = joint2.value
I feel like both approaches are pretty inefficient and could be improved. Aside from reconstructing the database, are there any more efficient solutions?