CODEWITHSUNDEEP
mysqlleft-joinmissing-dataself-join
sumeetk
sumeetk

Reputation: 11

SQL join with missing rows

I have the following two tables.

SurveyTable:

 QID |  Text   
----------------------------------------
  1  | Favorite movie
  2  | Favorite book
  3  | Favorite city

SurveyResponses: 

 UserID | QID |  Answer   
----------------------------------------
  1001  | 1  | StarWars
  1001  | 2  | Harry Potter
  1001  | 3  | Los Angeles
  1003  | 3  | New York

I would like to get a response which also has all the questions that the User did not answer in the survey.
Expected Output of SQL join: 

 UserID | QID |  Answer   
----------------------------------------
  1001  | 1  | StarWars
  1001  | 2  | Harry Potter
  1001  | 3  | Los Angeles
  1003  | 1  | -
  1003  | 2  | -
  1003  | 3  | New York

I tried various SQL query combinations but no luck. Please help.

Upvotes: 1

Views: 191

Answers (1)

Kris
Kris

Reputation: 41867

Since you don't provide a Users table, you may end up with something like this:

SELECT
    U.UserId, Q.QID, A.Answer
FROM SurveyTable Q
CROSS JOIN (SELECT DISTINCT UserId FROM SurveyResponses) U
LEFT JOIN SurveyResponses A ON A.QID = Q.QID AND U.UserId = A.UserId

You'll really want to avoid having to do this in real life though, the cross join is likely going to suck all performance out of the server if your tables get anywhere near large.

Better would be to have a Users table so you can use that and left join against it like this:

SELECT
    U.UserId, Q.QID, A.Answer
FROM Users U
INNER JOIN SurveyTable Q 
LEFT JOIN SurveyResponses A ON A.UserId = U.UserId AND A.QID = Q.QID

Upvotes: 1

Related Questions