Select data from other tables by foreign key

Question

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)

https://stackoverflow.com/questions/8567288/sql-server-inner-join-with-distinct i think this would help you — Md. Tazbir Ur Rahman Bhuiyan, Oct 14 '21 at 11:55

score 0 · Accepted Answer · answered Oct 14 '21 at 11:54

0

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

answered Oct 14 '21 at 11:54

Akina

39,301
5
14
25

Thank you. :) I tried to paste your code and it didn't work (php file and phpmyadmin). Then I typed it into my earlier sql string the code and everthing was ok. I compared the strings and your "c" chars after "JOIN colors" are different from my local "c". :) I have to rewrite that 3 "c" letter. – Gabreil Oct 14 '21 at 13:13

Select data from other tables by foreign key

1 Answers1