MySQL：如何选择所有不匹配 LEFT JOIN 的结果

发布于 2024-12-05 18:36:37 字数 395 浏览 1 评论 0原文

我的 SELECT 语句本质上是：

SELECT *, user.user, response.survey FROM survey, user, response ORDER BY survey.added

但是，我试图仅选择尚未答复的调查。 ”表包含 4 个相关列（用户 ID [响应调查的用户]、答案、问题 [如果是单个问题，则与调查 ID 相同，如果是多个问题，则对应于问题 ID] 和调查 ID）

“响应编写一条 SQL 语句，选择没有来自任意用户 ID ($_SESSION['userId']) 的响应的所有调查...尝试使用各种 LEFT JOIN 以及嵌套 SELECT 命令来解决此问题，但还没有' t能够弄清楚。

任何人都可以阐明如何解决这个问题吗？

原文

My SELECT statement is essentially:

SELECT *, user.user, response.survey FROM survey, user, response ORDER BY survey.added

However, I am trying to select only surveys that haven't been answered. The 'response' table contains 4 relevant columns (user ID [of user responding to survey], answer, question [same as survey ID if single question, if multi, corresponds to question ID], and survey ID)

I'm trying to write an SQL statement that selects all surveys that don't have a response from an arbitrary user ID ($_SESSION['userId'])... Tried going about this using various LEFT JOIN as well as nested SELECT commands, but haven't been able to figure it out.

Can anyone shed some light on how to go about this?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

泅人 2024-12-12 18:36:37

或者，只需左连接并检查右侧的列是否为 null：

SELECT *, user.user, response.survey FROM user 
LEFT JOIN response ON response.user=user.user 
LEFT JOIN survey ON survey.surveyID=response.surveyID 
WHERE user.user=[userID from session] AND response.user IS NULL ORDER BY survey.added

因为如果响应表中的匹配列丢失，response.user 将为 NULL。

Or, just left join and check the column on the right for null:

SELECT *, user.user, response.survey FROM user 
LEFT JOIN response ON response.user=user.user 
LEFT JOIN survey ON survey.surveyID=response.surveyID 
WHERE user.user=[userID from session] AND response.user IS NULL ORDER BY survey.added

Because if the matching column from the response table is missing, response.user would be NULL.

回复收藏 0 原文

一腔孤↑勇 2024-12-12 18:36:37

您编写的此 Select 语句：

SELECT *, user.user, response.survey FROM survey, user, response ORDER BY survey.added

正在执行交叉联接（获取用户、响应和调查的所有可能组合），这显然不是表之间引用数据的方式；因此是错误的。您需要通过公共键连接这 3 个表。

但要回答你的问题......

如果有一个表包含来自特定用户的响应；然后执行如下操作：

select * from survey s where s.survey_id not in (
select survey_id from response where userId=<particular_user_id>)
and s.user_id=<particular_user_id>

这将返回用户未回复的所有调查。

我希望这个想法是清楚的。

This Select statement you wrote:

SELECT *, user.user, response.survey FROM survey, user, response ORDER BY survey.added

Is doing a cross-join (getting all possible combinations of user,response and survey) and it's clearly not the way the data is referenced between the tables; therefore is wrong. You would need to join the 3 tables by a common key.

But to answer your question...

If there's a table with responses from a particular user; then do something LIKE this:

select * from survey s where s.survey_id not in (
select survey_id from response where userId=<particular_user_id>)
and s.user_id=<particular_user_id>

And that will return all surveys that the user has not responded.

I hope the idea is clear.

回复收藏 0 原文

~没有更多了~