MySQL Join Multiple IDs From Another Table

Question

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.

Terrible design for table 2, I'm not surprised you have issues working with that table. Fix it first, think about DB Normalization — Cid
– Cid, Commented Dec 1, 2021 at 10:31
I have to second what Cid said. Table 2 makes no sense. Did you create it? If so, you should redo that part. Make a table with 3 columns: ID, ShapeID and Color. — KIKO Software
– KIKO Software, Commented Dec 1, 2021 at 10:34
Unfortunately I didn't, I'd much perfer to do what you've suggested and then just use a standard inner join. — Layer 8
– Layer 8, Commented Dec 1, 2021 at 10:35
@Layer8 good luck then, and I mean it. You can try to find the person who designed that and yell at him/her/whatever gender, especially the day you'll realize you need 42 more fields in your aplication — Cid
– Cid, Commented Dec 1, 2021 at 10:40

ScaisEdge · Accepted Answer · 2021-12-01 10:31:44Z

1

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

answered Dec 1, 2021 at 10:31

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL Join Multiple IDs From Another Table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related