Joining different tables depending on column value

Question

I have a table R which contains a foreign key of a table P. In table P there is a column, telling me of which type a record in table P is. Related to the possible values in column P.type according tables exist. So, if in P.type is 'C' it represents a table named C, if the value is 'D' it represents a table named D.

Now I want to have a SELECT statement which gives me values from table C or D respectively, depending on P.type. To make it more complex, the number/types of columns in the result shall differ.

SELECT r.id, r.pid, p.type, 
FROM R r 
LEFT JOIN P p ON r.pid = p.pid 
WHERE r.id = 123 LIMIT 1;

Now I've got the record of the P table. Depending on the value of column type I want to decide which table (C or D) I want to join in the prior query. If I have to join table D the returned columns (resultset) shall be as it is but if table C must be joined, a further column shall be returned. Hence, the SELECT clause changes from

SELECT r.id, r.pid, p.type FROM ...

to

SELECT r.id, r.pid, p.type, c.name FROM ...

Preudocode:

SELECT r.id, r.pid, p.type(, c.name)   -- c.name my or may not be in the result depending on the joined table
FROM R r 
LEFT JOIN P p ON r.pid = p.pid 
if p.type = 'D' LEFT JOIN D d ON d.pid = p:pid end
if p.type = '' LEFT JOIN C c ON c.pid = p:pid end
WHERE r.id = 123 LIMIT 1;

Sind it is possible that the number of possible tables to join can extend (the values of p.type may increase from C and D to E, F, G, ...) I'd prefer to only join the ONE table specified by p.type rather that using a UNION above all possible tables. Whereby...since the number of columns in the result my differ, a UNION attempt ended in an Error Code 1222:

The used SELECT statements have a different number of columns.

Is this possible... and how?

Answer 1

Your pseudo-code can be implemented as:

SELECT r.id, r.pid, p.type, coalesce(d.name, c.name)  
FROM R r LEFT JOIN
     P p
     ON r.pid = p.pid LEFT JOIN 
     D d
     ON p.type = 'D' and d.pid = p.pid LEFT JOIN
     C c
     ON p.type = '' and c.pid = p.pid end
WHERE r.id = 123
LIMIT 1;

This will work, assuming that the tables C and D don't have multiple rows that match. If that is the case, you might need to pre-aggregate the results or use subqueries.

Answer 2

A similar but alternative answer to @GordonLinoff's answer is to combine tables C and D into a single table.

That could be done with a real single table, or a view, or an inline view, the latter of which I'll show below...

SELECT
  r.id,
  r.pid,
  p.type,
  z.name
FROM
  R   AS r
LEFT JOIN
  P   AS p
    ON r.pid = p.pid
LEFT JOIN
(
  SELECT 'D' AS type, pid, ''   AS name FROM D
  UNION ALL
  SELECT ''  AS type, pid, name AS name FROM C
)
  AS z
    ON  z.type = p.type
    AND z.pid  = p.pid
WHERE
  r.id = 123
LIMIT
  1
;

This structure can often simplify code by reducing repetition, but can also aid the optimiser (depending on indexes, etc) in reducing the workload necessary in the joins.

Note: Because the Unioned tables have scalar constants in the first field, and because that field is used in the join, the optimiser is able (depending on the RDBMS) to avoid attempting to redundantly join on both tables. (Enough information is present to 'know' which table to join on.)

EDIT : Following a comment made by the OP

If the two tables vary significantly enough to make the UNION ALL impractical, then using two LEFT JOINs may be you best option.