SQL:在一个表中查找同一个字段，在另一个表中查找多个值?

(Not sure if the name of this question really makes sense, but what I want to do is pretty straightforward)

(不确定这个问题的名字是否有意义，但我想做的很简单)

Given tables that looks something like this:

给定的表是这样的:

Table Foo
---------------------------------
| bar1_id | bar2_id | other_val |
---------------------------------

Table Bar
--------------------
| bar_id | bar_desc|
--------------------

How would I create a select that would return a table that would look like the following:

如何创建一个选择，返回如下所示的表:

---------------------------------------------------------
| bar1_id | bar1_desc | bar2_id | bar2_desc | other_val |
---------------------------------------------------------

i.e. I want to grab every row from Foo and add in a column containing the description of that bar_id from Bar. So there might be some rows from Bar that don't end up in the result set, but every row of Foo should be in it.

例如，我想从Foo获取每一行，并添加一个列，其中包含Bar对bar_id的描述。所以可能会有一些行来自于Bar，而不是结果集，但是每一行Foo都应该在它里面。

Also, this is postgres, if that makes a difference.

而且，这是邮差，如果这有什么区别的话。

5 个解决方案

#1

SELECT F.bar_id1, 
    (SELECT bar_desc FROM Bar B WHERE (F.bar_id1 = B.bar_id)),
    F.bar_id2, 
    (SELECT bar_desc FROM Bar B WHERE (F.bar_id2 = B.bar_id)),
    F.other_val
FROM FOO F;

#2

This doesn't directly answer your question (but that's ok, the people above already have), but...

这并不能直接回答你的问题(不过没关系，上面的人已经有了)，但是……

This is considered very bad design. What happens in the future when your foo can be associated with 3 bars? Or more? (Don't say it will never happen. I've lost count of the number of "that'll never happen" things I've implemented over the years).

这被认为是非常糟糕的设计。未来当你的foo可以与3小节关联时会发生什么?或者更多?不要说这永远不会发生。我已经记不清这些年来我实现的“永远不会发生”的事情的数量了)。

The generally correct way to do this is to do a one-to-many relationship (either with each bar pointing back to a foo, or an intermediate foo-to-bar table, see many-to-many relationships). Now you correctly format output on the front end, and just fetch a list of bars per foo to pass up to it (easy to do in SQL). Reports are a special case, but it's still relatively easily accomplished with pivoting or CrossTab queries.

通常正确的做法是进行一对多关系(每个bar都指向一个foo，或者中间的foo-to-bar表，请参见多对多关系)。现在，您可以正确地格式化前端的输出，并且只需为每个foo获取一个条列列表来传递给它(在SQL中很容易做到)。报告是一种特殊的情况，但它仍然相对容易地通过旋转或交叉查询来完成。

#3

SELECT
  foo.bar1_id, bar1.bar_desc AS bar1_desc,
  foo.bar2_id, bar2.bar_desc AS bar2_desc,
  foo.other_val
FROM
  foo
  INNER JOIN bar bar1 ON bar1.id = foo.bar1_id
  INNER JOIN bar bar2 ON bar2.id = foo.bar2_id

This assumes you'll always have both a bar1_id and a bar2_id in foo. If these can be null then change INNER JOIN to LEFT OUTER JOIN.

这假设在foo中总是有bar1_id和bar2_id。如果这些可以为空，那么将内部连接更改为左外部连接。

#4

select f.bar1, b1.desc, f.bar2, b2.desc, f.value 
from foo as f, bar as b1, bar as b2 
where f.bar1 = b1.id 
  and f.bar2 = b2.id

#5

I would try with information_schema.colums table.

我会尝试使用information_schema。科勒姆表。

    SELECT concat(table_name,'_',column_name) 
FROM information_schema.columns WHERE table_name = bar1 OR table_name = bar2
into new_table

Then you can populate it.

然后你可以填充它。

with foo as select * from bar1

使用foo从bar1中选择*

or select into

或选择到

#1

SELECT F.bar_id1, 
    (SELECT bar_desc FROM Bar B WHERE (F.bar_id1 = B.bar_id)),
    F.bar_id2, 
    (SELECT bar_desc FROM Bar B WHERE (F.bar_id2 = B.bar_id)),
    F.other_val
FROM FOO F;

#2