CODEWITHSUNDEEP
sqlmysqldatabase
Alan M.
Alan M.

Reputation: 1369

Fetch records from one table where there's not a record in another

SURVEYS table:

SurveyID
UserID
Question
Choice1
Choice2
Choice3

RESPONSES table:

UserID
SurveyID
Answer

The first desire (achieved): Show me all surveys that User 28 has initiated: 

SELECT * 
  FROM Surveys 
 WHERE Surveys.UserID = 28

The second desire (achieved): Show me all surveys that User 28 has answered: 

SELECT * 
  FROM Surveys 
INNER JOIN Responses ON Surveys.SurveyID = Responses.SurveyID 
 WHERE Responses.UserID = 28

The third desire (not achieved):

Show me all surveys that were NOT initiated by User 28 and which User 28 has NOT already answered... SELECT * FROM Surveys INNER JOIN Responses ON Surveys.SurveyID = Responses.SurveyID WHERE Surveys.UserID <> 28 AND Responses.UserID <> 28 [alternately: WHERE NOT Surveys.UserID = 28 OR Responses.UserID = 28]

The third query eliminates a record for User 28 but other instances of the same survey will appear. For instance, let's say that User 29 answered the survey. A row would be returned because the WHERE doesn't prohibit User 29's record.

I thought of using a subquery -- something like: SELECT * FROM Surveys WHERE Surveys.UserID <> 28 AND Surveys.SurveyID <> (SELECT Responses.SurveyID WHERE Responses.UserID = 28) -- but that doesn't work because the sub-query can easily produce more than one row.

What's the solution?

Upvotes: 2

Views: 940

Answers (2)

danielrsmith
danielrsmith

Reputation: 4060

Something like this I think should do? 

SELECT * 
FROM Surveys s
WHERE s.UserID != 28 
AND s.SurveyID NOT IN (SELECT R.SurveyID FROM Responses R WHERE R.UserID = 28)

Upvotes: 0

OMG Ponies
OMG Ponies

Reputation: 332571

Using NOT IN:

SELECT s.*
  FROM SURVEYS s
 WHERE s.userid != 28
   AND s.surveyid NOT IN (SELECT r.survey_id
                            FROM RESPONSES r
                           WHERE r.userid = 28)

Using LEFT JOIN/IS NULL:

   SELECT s.*
     FROM SURVEYS s
LEFT JOIN RESPONSES r ON r.survey_id = s.surveyid
                     AND r.user_id = 28
    WHERE s.userid != 28
      AND r.userid IS NULL

Using NOT EXISTS:

SELECT s.*
  FROM SURVEYS s
 WHERE s.userid != 28
   AND NOT EXISTS (SELECT NULL
                     FROM RESPONSES r
                    WHERE r.userid = 28
                      AND r.survey_id = s.surveyid)

Of the options listed, the NOT IN and LEFT JOIN/IS NULL are equivalent though I prefer the NOT IN because it is more readable.

Upvotes: 6

Related Questions