根据列值查询另一个表中的值

I am attempting to create a query that pulls information from two other tables, however I only know which table to pull from based on a column in another table. I'm currently looking into doing this using a stored procedure (e.g. build the query and then run it) but I wanted to know if there is a better way to do this, or if I could accomplish it in a single query.

我正在尝试创建一个从其他两个表中提取信息的查询，但是我只知道根据另一个表中的列提取哪个表。我目前正在考虑使用存储过程（例如构建查询然后运行它），但我想知道是否有更好的方法来执行此操作，或者我是否可以在单个查询中完成它。

In terms of the connections, ID's are unique accross the entire database, so no two ID's will overlap. However I do not know which subtable the ID relates. I am able to find this by pulling in an unrelated table that happens to have the information (call it the Object Table). One of the columns will give me the table name for the information (in my example below, Person). I have drafted a simple example below. Can you see any way I could accomplish this in a single query? Something like this is what I am aiming for but I am starting to think its not possible.

就连接而言，ID在整个数据库中是唯一的，因此没有两个ID会重叠。但是我不知道ID涉及哪个子表。我可以通过拉入一个碰巧有信息的无关表（称之为对象表）来找到它。其中一列将为我提供信息的表名（在我的示例中，Person）。我在下面起草了一个简单的例子。你能看到我在单个查询中完成此任务的方法吗？像这样的东西是我的目标，但我开始认为它不可能。

SELECT * FROM base_table 
LEFT JOIN object ON object.id = base_table.role 
LEFT JOIN [object.type] tmp ON tmp.entity_id = base_table.entity_id

id | role | entity_id        (Base Table)
---------------------
1  | 101  | 1000


id | type                   (Objects Table)
------------
101| person

entity_id | name | etc..    (Person Table)
------------------------
1000      | Bob  | ...

I also expect unions might be a possible solution - but other then just joining all the possible tables and parsing the columns to match up properly (which it could be as many as 20 tables) I'd rather not. This solution is also a bit of a nusience since the columns don't always match in a good way (e.g. the Person table doesn't have similar columns to the Address table)

我也期望工会可能是一个可能的解决方案 - 但其他只是加入所有可能的表并解析列以正确匹配（它可能多达20个表）我宁愿不。这个解决方案也有一点点因素，因为列并不总是以一种好的方式匹配（例如，Person表没有与Address表类似的列）

2 个解决方案

#1

I don't think the left join idea is that bad if you just ignore object type.

如果你只是忽略对象类型，我认为左连接的想法是不好的。

Since each ID is unique you don't need to look at type at all if you use coalesce. So to use @TT model as an example:

由于每个ID都是唯一的，因此如果使用coalesce，则根本不需要查看类型。所以以@TT模型为例：

SELECT bt.*,
  COALESCE(P.f1, L.f1, C.f1) AS f1,
  -- ...,
  COALESCE(P.fn, L.fn, C.fn) AS fn
FROM
  base_table AS bt
  LEFT JOIN Person AS P ON P.entity_id = bt.entity_id
  LEFT JOIN [Legal Person] AS L ON L.entity_id = bt.entity_id
  LEFT JOIN Counterpart AS C ON C.entity_id = bt.entity_id

Depending on your data size and indexes this might perform faster or the same as TT's example -- remember there is only 1 select with N joins while TT's has N selects, 2N joins. It really depends on your data.

根据您的数据大小和索引，这可能比TT的示例更快或更相同 - 记住只有1个选择N个连接，而TT有N个选择，2N连接。这真的取决于你的数据。

If there is some field (fz) that does not show up in all types then you just don't inlcude that in the coalesce clause.

如果有一些字段（fz）没有出现在所有类型中，那么你就不会在coalesce子句中包含它。

I think this style might be easier to maintain and understand and will be the same or faster as TT code.

我认为这种风格可能更容易维护和理解，并且与TT代码相同或更快。

#2

What you probably want to do is the following: for each possible detail-table (ie the possible values in [object.value]), write a query that only links with that one detail-table and have a WHERE clause to restrict to the proper entities. Then do a UNION ALL for all those queries.

您可能想要做的是以下内容：对于每个可能的详细信息表（即[object.value]中的可能值），编写一个仅与该一个详细信息表链接并且具有WHERE子句以限制为适当的实体。然后为所有这些查询执行UNION ALL。

Say you have Person, Legal Person and Counterpart as possible values in [object.type]. Suppose the detail-tables have the same names. You can write:

假设您在[object.type]中有Person，Legal Person和Counterpart作为可能的值。假设detail-tables具有相同的名称。你可以写：

SELECT
  bt.*,
  dt.f1,
  -- ...,
  dt.fn
FROM
  base_table AS bt
  INNER JOIN object AS o ON o.id = bt.role
  INNER JOIN Person AS dt ON dt.entity_id = bt.entity_id
WHERE
   o.type='Person'
UNION ALL
SELECT
  bt.*,
  dt.f1,
  -- ...,
  dt.fn
FROM
  base_table AS bt
  INNER JOIN object AS o ON o.id = bt.role
  INNER JOIN [Legal Person] AS dt ON dt.entity_id = bt.entity_id
WHERE
   o.type='Legal Person'
UNION ALL
SELECT
  bt.*,
  dt.f1,
  -- ...,
  dt.fn
FROM
  base_table AS bt
  INNER JOIN object AS o ON o.id = bt.role
  INNER JOIN Counterpart AS dt ON dt.entity_id = bt.entity_id
WHERE
   o.type='Counterpart'

#1