从一个表中获取记录，而另一个表中没有记录 [英] Fetch records from one table where there's not a record in another

问题描述

SURVEYS 表：

  SurveyID 
 UserID 
 Question 
 Choice1 
 Choice2 
 Choice3 
  

RESPONSES 表：

  UserID 
 SurveyID 
 b $ b  

第一个愿望（实现）：显示用户28发起的所有调查：

  SELECT * 
 FROM Surveys 
 WHERE Surveys.UserID = 28 
  pre> 
 
 
第二个愿望（实现）：显示用户28已回答的所有调查：
  SELECT * 
 FROM Surveys 
 INNER JOIN回应ON Surveys.SurveyID = Responses.SurveyID 
 WHERE Responses.UserID = 28 
  
 
 
 
第三个愿望（未实现）：
 
 
 
显示未启动的所有调查由用户28和用户28尚未回答... SELECT * FROM Surveys INNER JOIN响应ON Surveys.SurveyID = Responses.SurveyID WHERE Surveys.UserID<> 28和Responses.UserID<> 28 [交替地： Surveys.UserID = 28 OR Responses.UserID = 28] 
 
 
 
第三个查询消除了用户28的记录，但会出现同一测量的其他实例。例如，假设用户29回答了调查。将返回一行，因为WHERE不禁止用户29的记录。
 
 
 
我想到使用子查询 - 类似：SELECT * FROM Surveys WHERE Surveys.UserID <> 28和Surveys.SurveyID<>（SELECT Responses.SurveyID WHERE Responses.UserID = 28） - 但是不起作用，因为子查询可以容易地产生多于一行。
 
 
 
解决方案是什么？
解决方案
 
使用NOT IN：
 
 
 
  SELECT s。* 
 FROM SURVEYS s 
 WHERE s.userid！= 28 
 AND s.surveyid NOT IN r.survey_id 
 FROM RESPONSES r 
 WHERE r.userid = 28）
  
 
 
 
使用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 
  
 
 
 
使用NOT EXISTS：
 
 
 
  SELECT s。 
 FROM SURVEYS s 
 WHERE s.userid！= 28 
和NOT EXISTS（SELECT NULL 
 FROM RESPONSES r 
 WHERE r.userid = 28 
 AND r.survey_id = s.surveyid）
  
列出的选项中， NOT IN 和 LEFT JOIN / IS NULL 是等同的，但我更喜欢 NOT IN 更易读。
 
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?
解决方案
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.

                        
这篇关于从一个表中获取记录，而另一个表中没有记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！