Help forming a complex SQL statement

Question

Hello friends!

I'm coming up stumped on forming a working SQL statement for a project of mine. First of all, I have a "questions" table and a "response" table. The response table is tied to the question table through a foreign id called "question_id" (tied to the questions "id"). A question doesn't necessarily have to have a response, but the only working statement I can come up with will only pull a question that has a response, but I need it to show every question whether or not there is a response.

That SQL statement is:

SELECT u.firstname, q.question, r.tutor_id, r.response FROM response r 
JOIN question q ON q.id = r.question_id
JOIN user u ON u.id = q.user_id

My other problem is that I'm also trying to pull the firstname of the tutor, but can only pull the "tutor_id", so any help with that would also be awesome. If anyone has any tips, I'd appreciate it!

Edit: Thanks so much guys (or gals)!

Works with:

SELECT u.firstname, q.question, v.firstname, r.response FROM question q INNER JOIN user u ON u.id = q.user_di LEFT JOIN response r ON q.id = r.question_id LEFT JOIN user v ON v.id = r.tutor_id

Answer 1

You're looking for a LEFT JOIN instead of an inner join. This will return all values on the first table regardless of whether or not it matches one from the second table.

As for question two, it looks like you need a tutor table to join on to get the name. Does one exist?

SELECT u.firstname, q.question, r.tutor_id, r.response, u2.firstname AS TutorName FROM response r 
LEFT JOIN question q ON q.id = r.question_id
JOIN user u ON u.id = q.user_id
JOIN user u2 on u2.id = r.tutor_id

Answer 2

An INNER JOIN will cull out any non joining data. Use a LEFT JOIN instead, and you will get all of the rows from the "left hand side".

SELECT 
    u.firstname, 
    q.question, 
    r.tutor_id, 
    r.response 
FROM 
    question q          // left hand side

    INNER JOIN user u       // assumes all questions have a user
    ON u.id = q.user_id

    LEFT JOIN  response r 
    ON q.id = r.question_id 

Where is the tutor name stored?

OK, I'm not sure why some of the other solutions aren't quite working for you, but you can always do something like this to include tutor name:

SELECT 
        u.firstname, 
        q.question, 
        T.tutor_id,
        T.firstname, 
        T.response 
    FROM 
        question q          // left hand side

        INNER JOIN user u       // assumes all questions have a user
        ON u.id = q.user_id

        LEFT JOIN (
                SELECT
                    r.response,        
                    r.question_id,
                    r.tutor_id
                    u2.firstname
                FROM
                    response r 

                    INNER JOIN user u2
                    ON r.tutor_id = u2.id   // Is there a user type we can also join on?
            ) T 
        ON q.id = T.question_id 

Answer 3

SELECT u.firstname, q.question, r.tutor_id, r.firstname as tutorname, r.response FROM 
response r RIGHT JOIN (question q INNER JOIN user u ON u.id = q.user_id)
ON q.id = r.question_id

Answer 4

Give this a shot. I don't know what tutor_id is joined to, but I assume it's the user id?

SELECT u.firstname, q.question, v.firstname, r.response FROM question q
LEFT JOIN q ON q.id = r.question_id
LEFT JOIN user u ON u.id = q.user_id
LEFT JOIN user v ON v.id = r.tutor_id

See that I grabbed the firstname of the user that answered (I think). I made that v (distinguishing it from the user u).

EDIT: I am kind of assuming that a "tutor" is just another user that answers the question.