I have a scenario which I'm a bit stuck on. Let's say I have a survey about colors, and I have one table for the color data, and another for people's answers.
tbColors
color_code , color_name
1 , 'blue'
2 , 'green'
3 , 'yellow'
4 , 'red'
tbAnswers
answer_id , favorite_color , least_favorite_color , color_im_allergic_to
1 , 1 , 2 3
2 , 3 , 1 4
3 , 1 , 1 2
4 , 2 , 3 4
For display I want to write a SELECT that presents the answers table but using the color_name column from tbColors.
I understand the "most stupid" way to do it: naming tbColors three times in the FROM section, using a different alias for each column to replace.
How would a non-stupid way look?
This seems like the way to go:
SELECT
A.answer_id
,C1.color_name AS favorite_color_name
,C2.color_name AS least_favorite_color_name
,C3.color_name AS color_im_allergic_to_name
FROM tbAnswers AS A
INNER JOIN tbColors AS C1
ON A.favorite_color = C1.color_code
INNER JOIN tbColors AS C2
ON A.least_favorite_color = C2.color_code
INNER JOIN tbColors AS C3
ON A.color_im_allergic_to = C3.color_code
Rather than "stupid", I'd venture that this is a pretty standard query. This also presumes that all columns will have a valid value. Otherwise, replace all INNER JOINs with LEFT JOINs