MySQL Join Multiple IDs From Another Table-CodePudding

I've spent hours trying to work out how to do this for my project, but I can't seem to find either, a good way of explaining it in order to research the correct thing, or an answer to my question, hence the post here.

I have two tables as shown below, one with an ID and the name, and another with fields relating to those IDs. I need to be able to match the ID to the name.

Table 1:

ID | Name
---|----------
1  | Square
2  | Rectangle
3  | Circle

Table 2:

ID | Field1ID | Field1Data | Field2ID | Field2Data | Field3ID | Field3Data
---|----------|------------|----------|------------|----------|-----------
1  | 1        | Red        | 3        | Green      | 2        | Blue

I need to be able to match the FieldxID's in table 2 to the name's in table 1 creating the following:

ID | Field1ID | Field1Data | Field2ID | Field2Data | Field3ID  | Field3Data
---|----------|------------|----------|------------|-----------|-----------
1  | Square   | Red        | Circle   | Green      | Rectangle | Blue

Any help would be appreciated.

CodePudding user response：

you need several joins, one for each fieldID

select b.Name, a.Field1Data, c.name, a.Field2Data, d.name, a.FieldeData
from table2 a
inner join table1 b on b.id = a.Field1ID
inner join table1 c on c.id = a.Field2ID
inner join table1 d on d.id = a.Field3ID