根据列值连接不同的表 [英] Joining different tables depending on column value

问题描述

我有一个表R，其中包含表P的外键.表P中有一列，告诉我表P中的记录是哪种类型.存在与表中列P.type中的可能值相关的信息.因此，如果P.type中的'C'代表一个名为C的表，如果'D'的值则代表一个名为D的表.

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.

现在，我想拥有一条SELECT语句，该语句根据P.type分别为我提供来自表C或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;

现在我有了P表的记录.根据列类型的值，我想确定要在先前查询中联接的表(C或D). 如果必须连接表D，则返回的列(结果集)应保持原样，但如果必须连接表C，则应返回另一个列.因此，SELECT子句从

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

选择r.id，r.pid，p.type FROM ...

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

到

选择r.id，r.pid，p.type，c.name FROM ...

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

Preudocode:

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;

发现可能要加入的表数量可以扩展(p.type的值可能从C和D增加到E，F，G等)，我宁愿只加入一个由p.type指定的表，而不是在所有可能的表上方使用UNION. 从而...由于结果中的列数可能不同，因此UNION尝试以错误代码1222结尾:

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:

使用的SELECT语句具有不同的列数.

这有可能吗?怎么办?

推荐答案

您的伪代码可以实现为:

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;

这将起作用，假设表C和D没有匹配的多行.在这种情况下，您可能需要预先汇总结果或使用子查询.

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.

这篇关于根据列值连接不同的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！