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;

  linktable CHAR(64) NOT NULL
INSERT INTO parent VALUES (1, 'child1'), (2, 'child2');

  data CHAR(10)
INSERT INTO child1 VALUES (1, 'abc');

  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:

    CASE linktable
      WHEN 'child1' THEN
      WHEN 'child2' THEN
      ELSE 'Error'
    END AS Data
FROM parent AS p
LEFT JOIN child1 AS c1 ON
LEFT JOIN child2 AS c2 ON;
| 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)

Return to the Artful Common Queries page