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?

Answer 1

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 A
INNER JOIN tbColors C1
  ON A.favorite_color = C1.color_code
INNER JOIN tbColors C2
  ON A.least_favorite_color = C2.color_code
INNER JOIN tbColors 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

Answer 2

Can you help me with mine?

I have two tables and trying to inner join just like above but having all kings of problems.

Table 1 is destinations which have destinationid, destination, address, city, state, zip

Table 2 is deliveries which has delid, destination, splitdestination, etc....

My goal is to get the destination and splitdestination to populate with the destination name. Destination and splitdestination are both populated with the destinationid as an index value.

I have tried many ways to get this to work. I can inner join the destination just fine but when it comes to also populating splitdestination I get no where. Any help?