Data-driven joins
Data-driven table relationships are hard to maintain, but sometimes they cannot be avoided. How do we build joins for them? One way is to use a CASE statement in the SELECT list to handle the joining possibilities. In this example, the parent.linktable column determines the name of the table where a particular parent row's data is. The method is fine when the number of child tables is small:
USE test;
DROP TABLE IF EXISTS parent, child1, child2;
CREATE TABLE parent (
id INT UNSIGNED PRIMARY KEY,
linktable CHAR(64) NOT NULL
);
INSERT INTO parent VALUES (1, 'child1'), (2, 'child2');
CREATE TABLE child1 (
id INT UNSIGNED PRIMARY KEY,
data CHAR(10)
);
INSERT INTO child1 VALUES (1, 'abc');
CREATE TABLE child2 (
id INT UNSIGNED PRIMARY KEY,
data CHAR(10)
);
INSERT INTO child2 VALUES (2, 'def');
To retrieve all child data for all parents, include in the SELECT list a CASE statement which handles all child table possibilities:
SELECT
p.id,
p.linktable,
CASE linktable
WHEN 'child1' THEN c1.data
WHEN 'child2' THEN c2.data
ELSE 'Error'
END AS Data
FROM parent AS p
LEFT JOIN child1 AS c1 ON p.id=c1.id
LEFT JOIN child2 AS c2 ON p.id=c2.id;
+----+-----------+------+
| id | linktable | Data |
+----+-----------+------+
| 1 | child1 | abc |
| 2 | child2 | def |
+----+-----------+------+
When the number of child tables is too large for a convenient CASE statement, PREPARE the query in a stored procedure.
(Based on a MySQL Forum post by Felix Geerinckx)