How to do joins on multiple columns in mysql table-CodePudding

I have a 'response' table with many columns, some of those columns take an integer value which corresponds to another 'answer' table. Here is the response table:

TABLE response:

name      | answer1 | answer2 | answer3
----------------------------------
james foo | 1       | 2       | 3

TABLE answers_ref:

id | answer 
------------
1  | Yes
2  | No
3  | Maybe

So I want to be able to get the answers from james foo, the results should be

name      | answer1 | answer2 | answer3
----------------------------------
james foo | Yes     | No      | Maybe

Here is the query that i tried:

select t1.name, al.answer, al2.answer FROM response t1 LEFT JOIN answers_ref al ON t1.answer1 = al.id LEFT JOIN answers_ref al2 ON t1.answer2 = al2.id;

The above works to get 2 answers, but I have to do a third join to get answer3, but what if I have 10 answers/columns?

CodePudding user response：

This one works. I tested:

SELECT r.name, 
  (SELECT answer FROM answers_ref WHERE id = r.answer1) as answer1, 
  (SELECT answer FROM answers_ref  WHERE id = r.answer2) as answer2, 
  (SELECT answer FROM answers_ref  WHERE id = r.answer3) as answer3
FROM response r
WHERE name = 'James Foo';

CodePudding user response：

You canot achieve that with less joins. Each column is treated separately and they don't magically somehow share "join" values between them.

The rue is simple - one join per column, that's it. You can't get any simplier or shorter than this.

So the answer is: that's not possible.