Select data from other tables by foreign key-CodePudding

I have 3 tables:

Users:

id	name
1	John
2	Carol

Colors:

id	name
101	Red
102	Black
103	Blue
104	Yellow

Votes

id	user	vote_01	vote_02	vote_03
1	2	103	101	104

I would like to see this result

id	name	vote_01	vote_02	vote_03
1	Carol	Blue	Red	Yellow

Now I have 3 row in the result set, but I want only 1. (vote_01, _02, _03 have hiearchy, i want to store them in one row / user )

SELECT votes.id, users.name AS username, colors.name 
FROM votes 
JOIN users ON votes.user=users.id 
JOIN colors ON (votes.vote_01=colors.id OR votes.vote_02=colors.id OR votes.vote_03=colors.id)

CodePudding user response：

Votes colors are independent, so each separate vote index must be converted to according color with separate independent colors table's copy:

SELECT votes.id, 
       users.name AS username, 
       c1.name vote_01, 
       c2.name vote_02, 
       c3.name vote_03
FROM votes 
JOIN users ON votes.user=users.id 
JOIN colors с1 ON votes.vote_01=c1.id
JOIN colors с2 ON votes.vote_02=c2.id
JOIN colors с3 ON votes.vote_03=c3.id