Selecting attributes from multiple tables via foreign key

Question

Apologies if this question has already been answered. Couldn't find the answer myself.

I'm trying to work out the SQL to extract data from one table which is being referenced from another table. My explanation might be terrible due to looking at it way too long, so see my tables below:

Table 1	Table 2
(PK) ID (int)	(PK) ID (int)
Att_1 (str)	(FK) Att_1 (int)
Att_2 (str)	(FK) Att_2 (int)
Att_3 (str)	Att_3 (str)

Essentially I'm trying to select all from table 2, but table2.att_1 and table2.att_2 are foreign keys of table1.id. What I want to achieve is extract the following:

Value of Table2.ID

Table1.Att_1 where Table2.Att_1 = Table1.ID

Table1.Att_1 where Table2.Att_2 = Table1.ID

Value of Table2.Att_3

The final SQL will be used in a PHP script, so if its not possible to do this in a single line of SQL, I can split the requests up one by one. Just looking for some good old CPD.

Answer 1

Essentially I'm trying to select all from table 2, ...

So we start with Table2 as our basis for the query:

FROM Table2

... but table2.att_1 and table2.att_2 are foreign keys of table1.id.

We'll likely need two (outer) joins to the same table (Table1), one for each foreign key:

FROM Table2
    LEFT JOIN Table1 ..
    LEFT JOIN Table1 ..

What I want to achieve is extract the following: Value of Table2.ID

SELECT Table2.ID

Table1.Att_1 where Table2.Att_1 = Table1.ID

Table1.Att_1 where Table2.Att_2 = Table1.ID

Right, those verifies that we want 2 left joins and the ON conditions:

SELECT 
    Table1.Att_1
FROM Table2
    LEFT JOIN Table1 ON Table2.Att_1 = Table1.ID 

and

SELECT 
    Table1.Att_1
FROM Table2
    LEFT JOIN Table1 ON Table2.Att_2 = Table1.ID

Oops, we have a problem to combine the two joins. We'll need to alias those tables as we have a table appearing twice and it's good practice nonetheless:

FROM Table2 AS t2
    LEFT JOIN Table1 AS t1a ON t2.Att_1 = t1a.ID 
    LEFT JOIN Table1 AS t1b ON t2.Att_2 = t1b.ID

Value of Table2.Att_3

SELECT t2.Att_3

The whole query becomes (after adding some column aliases as well):

SELECT t2  . ID     AS  ID,
       t1a . Att_1  AS  Att_1_a,
       t1b . Att_1  AS  Att_1_b,
       t2  . Att_3  AS  Att_3
FROM Table2 AS t2
    LEFT JOIN Table1 AS t1a ON t2.Att_1 = t1a.ID 
    LEFT JOIN Table1 AS t1b ON t2.Att_2 = t1b.ID
  ;