如何使WHERE子句仅适用于左联接中的右表? [英] How can I make a WHERE clause only apply to the right table in a left join?

问题描述

我有两个桌子.

TableA: field_definitions
field_id, field_type, field_length, field_name, field_desc, display_order, field_section, active

TableB: user_data
response_id, user_id, field_id, user_response

我需要一个查询，该查询将返回表A中的所有行，如果存在，则会根据特定的user_id匹配表B中的行.

I need a query that will return all rows from table A and, if they exist, matching rows from table B based on a particular user_id.

这是我到目前为止所拥有的...

Here is what I have so far...

SELECT field_definitions. * , user_data.user_response
FROM field_definitions
LEFT JOIN user_data
USING ( field_id ) 
WHERE (
user_data.user_id =8
OR user_data.user_id IS NULL
)
AND field_definitions.field_section =1
AND field_definitions.active =1
ORDER BY display_order ASC 

仅当表B的WHERE子句中的user_id具有零行或匹配的行时，此方法才有效.如果表B的行具有匹配的field_id而不是user_id，则返回零行.

This only works if table B has zero rows or matching rows for the user_id in the WHERE clause. If table B has rows with matching field_id but not user_id, I get zero returned rows.

本质上，一旦表B中存在用户X的行，则在搜索用户Z响应时查询将不再从表A中返回行，并且找不到任何行.

Essentially, once rows in table B exist for user X, the query no longer returns rows from table A when searching for user Z responses and none are found.

即使在B中没有匹配的具有正确user_id的行，我也需要结果始终包含表A中的行.

I need the result to always contain rows from table A even if there are no matching rows in B with the correct user_id.

推荐答案

您可以将这些约束从WHERE子句移到ON子句(这首先需要将USING子句更改为子句:ON子句比USING子句灵活得多).所以:

You can move those constraints from the WHERE clause to the ON clause (which first requires that you change the USING clause into an ON clause: ON clauses are much more flexible than USING clauses). So:

SELECT field_definitions.*,
       user_data.user_response
  FROM field_definitions
  LEFT
  JOIN user_data
    ON user_data.field_id = field_definitions.field_id
   AND user_data.user_id = 8
 WHERE field_definitions.field_section = 1
   AND field_definitions.active = 1
 ORDER
    BY field_definitions.display_order ASC
;

这篇关于如何使WHERE子句仅适用于左联接中的右表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！