左联接中仅一个表的where子句 [英] Where clause for only one table in a left join

问题描述

我有两个表，第一个是"actions"，具有列"id"和"actionname"，第二个是"completedactions"，具有"userid"和"actionid".它们如下所示:

I have two tables, the first is 'actions' which has columns 'id' and 'actionname', the second is 'completedactions' which has 'userid' and 'actionid'. They look as follows:

Table: actions

 id | actionname
 ==============
  1 | An action
  2 | Another
  3 | ect
  4 | ect

actions is a table of actions a user can make

-

Table: completedactions

 userid | actionid
 ==============
   1    |  1
   1    |  2
   1    |  3
   2    |  3
   3    |  2
   3    |  4

completedactions holds the userid and action id for each action made

我想联接表以给出所有动作的列表，以及这些动作中的哪些动作是由给定用户在查询中指定的位置进行的.因此，从这个示例中，我希望看到类似的东西:

I want to join the tables to give a list of all actions and which of those actions have been made by a given user, specified somewhere in the query. So from this example I'd like to see something like:

 id | actionname | Complete by userid=1?
 =======================================
  1 | An action  | 1
  2 | Another    | 1
  3 | ect        | 1
  4 | ect        | Null

我尝试了左联接，并设法获得了所有动作的列表，但是当多个用户执行了一个动作时，会有重复的条目，每个用户都执行一个动作.然后，我在最后compledtedactions.userid ="1"处添加了where子句，但随后我丢失了该用户尚未执行的所有操作.

I've tried a left join and have managed to get a list of all actions but there are duplicate entries when multiple users have taken an action, one for each user to have made the action. I then added a where clause at the end compledtedactions.userid="1" but I then lose all actions that haven't been made by that user.

我似乎在左联接中仅对一个表没有where子句，那么我应该怎么做呢?

I can't seem to have a where clause for only one table in the left join so how else should I be going about this?

推荐答案

尝试在ON()子句中使用WHERE条件，即ON (c.actionid = a.id AND c.user_id = 1).

Try using your WHERE condition in your ON() clause instead, i.e., ON (c.actionid = a.id AND c.user_id = 1).

WHERE过滤器将应用于整个结果集，而联接中的其他条件将联接匹配的结果，并为不匹配的结果提供null.

The WHERE filter will apply on the whole resultset, while an additional condition in the join will join the matched results and give null for non-matched results.

SELECT a.*,c.user_id FROM 
actions a 
LEFT JOIN completedactions c 
ON (c.actionid = a.id AND c.user_id = 1)

这篇关于左联接中仅一个表的where子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！