Artful Common Queries

Data-driven joins

from the Artful Common Queries page

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)

Last updated 22 May 2024