SQL查询使用两列作为同一个表的外键

I have two tables

我有两张桌子

Table A

    id ! name ! fk_1_table_B_1 ! fk_2_table_B_2
-------|------|----------------|--------------
    1  | John | 1              | 3
    2  | Paul | 2              | 1
    3  | Anna | 4              | 2
    4  | Alan ! 3              | 1

Table B

    id | code
-------|------
    1  | EN
    2  | US
    3  | FR
    4  | IT

The idea is to obtain the following query

想法是获得以下查询

    id ! name ! code (fk_1_table_B_1) ! code (fk_1_table_B_2)
-------!------!-----------------------!-----------------
    1  | John | EN                    | FR
    2  | Paul | US                    | EN
    3  | Anna | IT                    | US
    4  | Alan ! FR                    | EN

If Table A had only one FK Column from Table B I would do

如果表A只有表B中的一个FK列,我会这样做

SELECT tableA, name, tableB.code 
FROM tableA, table B
WHERE tableA.fk_1_table_B_1 = tableB.id

How can I do this with Table A having two columns as FK from B? What should I select in the SELECT?EN

如何使用表A作为FK的两列来做到这一点?我应该在SELECT?EN中选择什么?

Thanks

2 个解决方案

#1

You should join to the same table twice, giving it two different aliases:

您应该两次加入同一个表,给它两个不同的别名:

SELECT a.id, a.name, b1.code, b2.code
FROM tableA a
JOIN tableB b1 ON b1.id = a.fk_1_table_B_1
JOIN tableB b2 ON b2.id = a.fk_2_table_B_2

Note how this query uses ANSI join syntax for better clarity: rather than listing all tables in the FROM clause, it puts each of the aliased tableBs in its own JOIN clause.

请注意此查询如何使用ANSI连接语法以更清晰:不是列出FROM子句中的所有表,而是将每个别名tableB放在其自己的JOIN子句中。

#2

Maybe this?

select a.id, a.name,
(select b.code from B b where b.id = a.fk_1_table_B_1),
(select c.code from B c where c.id = a.fk_1_table_B_2),
 from A a

#1