Performing Inner Join for Multiple Columns in the Same Table

Question

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?

related http://stackoverflow.com/questions/4267929/whats-the-best-way-to-join-on-the-same-table-twice?rq=1 — nawfal, Sep 02 '13 at 21:14

score 96 · Accepted Answer · edited Dec 05 '11 at 12:34

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

Performing Inner Join for Multiple Columns in the Same Table

1 Answers1

Linked