SQL join with missing rows

Question

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.

if you setup a sqlfiddle (sqlfiddle.com), people will be more likely to help, also showing what you already tried never hurts. — Kris
– Kris, Commented Dec 16, 2016 at 0:27

Kris · Accepted Answer · 2016-12-16 01:22:12Z

1

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

edited Dec 16, 2016 at 1:22

answered Dec 16, 2016 at 0:35

Kris

42.1k9 gold badges78 silver badges103 bronze badges

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

5 Comments

sumeetk Over a year ago

Kris, I do have a Users table. Infact my real problem involves joining 11 tables. Without making it too complicated, I wanted help in figuring out how to add the missing rows from 2nd table. Let me try the sql query you suggested. Thanks.

Kris Over a year ago

Had a typo in one of the join clauses, but the horrible cross join over distinct query does give me all the rows you asked for.

sumeetk Over a year ago

Kris, I have added the third Users table, however the 2nd query that you suggested did not work like expected. sqlfiddle.com/#!9/973239/3

Kris Over a year ago

yeah, I guess I was getting sleepy, was joining on columns that don't exist. The new version works in your fiddle though

philipxy Over a year ago

The cross join is not a problem, it is a requirement that the result UserId & QID columns be the cross join of the SurveyTable projection on QID and the SurveyResponses projection on UserID. But Users is not needed for the specified output.

Collectives™ on Stack Overflow

SQL join with missing rows

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related