How can I make the WHERE clause only apply to the right table in the 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, match rows from table B based on a specific user_id.
Here's 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 null rows or matching rows for user_id in the WHERE clause. If table B has rows with matching field_id but not user_id, I get null rows returned.
Essentially, once rows in table B exist for user X, the query no longer returns rows from table A when looking for answers from user Z, and none were found.
I need the result to always contain rows from table A, even if there are no matching rows in B in the correct user_id.
source to share
You can move these restrictions from offer WHERE
to offer ON
(which first requires you to change offer USING
to offer ON
: ON
much more flexible than that USING
). 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
;
source to share
Conceptually, the join is done first and then the where clause is applied to the virtual result set. If you want to filter one table first, you have to code it as a sub-selection inside the join. Something like this:
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
source to share
You can move the WHERE clause inside like this
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
source to share
Literal translation 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".
source to share