CODEWITHSUNDEEP
mysqlsqljoinleft-join
Corey
Corey

Reputation: 826

How can I make a WHERE clause only apply to the right table in a left join?

I have two tables.

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

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

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.

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.

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.

Upvotes: 2

Views: 2687

Answers (4)

Jim Garrison
Jim Garrison

Reputation: 86774

Conceptually, the join is performed first and then the where clause is applied to the virtual resultset. If you want to filter one table first, you have to code that as a sub-select inside the join. Something along these lines:

SELECT 
  field_definitions. * , 
  user8.user_response
FROM 
  field_definitions
    LEFT JOIN (select * from user_data where user_id=8 or user_id is null) as user8
    USING ( field_id ) 
WHERE
  field_definitions.field_section =1
  AND field_definitions.active =1
ORDER BY display_order ASC

Upvotes: 3

onedaywhen
onedaywhen

Reputation: 57023

A literal translation of the sepc:

SELECT field_definitions. * , '{{MISSING}}' AS user_response
  FROM field_definitions 
UNION
SELECT field_definitions. * , user_data.user_response
  FROM field_definitions 
       NATURAL JOIN user_data
 WHERE user_data.user_id = 8;

However, I suspect that you don't really want "all rows from table A".

Upvotes: 0

Chetter Hummin
Chetter Hummin

Reputation: 6817

You can move the WHERE clause inside as follows

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

Upvotes: 1

ruakh
ruakh

Reputation: 183371

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
;

Upvotes: 9

Related Questions