SQL query with two columns as foreign keys of the same table

Question

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

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

Thanks

Answer 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.

Answer 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