Menu
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?
415
Yes: You can use Inner Join to join on multiple columns.
The following example shows how to concatenate three different columns: (SELECT id, email1 AS email FROM customer) UNION (SELECT id, email2 AS email FROM customer) UNION (SELECT id, email3 AS email FROM customer) ORDER BY id, email; As you can see, it's important that all the queries return the same columns.
The self-join is a special kind of joins that allow you to join a table to itself using either LEFT JOIN or INNER JOIN clause. You use self-join to create a result set that joins the rows with the other rows within the same table.
If you'd like to get data stored in tables joined by a compound key that's a primary key in one table and a foreign key in another table, simply use a join condition on multiple columns. In one joined table (in our example, enrollment ), we have a primary key built from two columns ( student_id and course_code ).
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
115
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
