Selecting multiple values from multiple tables

Question

MySQL.

I have two tables, one is "Questions" and the other is "Answers"

The Questions table:

- question_id
- user_id
- question

The Answers table:

- answer_id
- question_id
- user_id
- answer
- correct

The goal is to get all questions (and associative answers) based on a user's id. I've been able to get all of the answers, however I'm only getting one question. I can see why it's only getting a single question, but I don't have any idea how to go about getting the question text for each answer.

Here's the code that I'm using right now. Where id_in is an input value on a saved procedure. The issue is that it gives me all of the answers for each question, but all of them return the same question text. I feel like possibly a type of join would be better here, but we haven't started learning about them yet and I hardly know anything about them as is.

BEGIN
    DECLARE question_text VARCHAR(40);
    SELECT question INTO question_text FROM questions WHERE user_id = id_in;
    SELECT question_text, Q.* FROM answers AS Q WHERE user_id = id_in;
END

Yes, this is homework. I'm just completely lost as to what I need to be doing.

RazorSky · Accepted Answer · 2015-10-02 15:40:20Z

1

Left joins allow for All things in the left table, and only the matching things in the right table. In my example I may have A and Q mixed up but I think this is the general gist of it. You can also take the user_id = in_id and move that to a wear, but filter on the join should be faster.

SELECT
    Q.QUESTION
,   A.ANSWER
,   A.CORRECT

FROM ANSWERS A
LEFT JOIN QUESTION Q 
ON  A.QUESTION_ID = Q.QUESTION_ID
AND A.USER_ID = Q.USER_ID
AND A.USER_ID = ID_IN
AND Q.USER_ID = ID_IN

answered Oct 2, 2015 at 15:40

RazorSky

3502 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Selecting multiple values from multiple tables

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related