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.
>Solution :
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