我正在尝试连接两列,其中条件基于右表中的删除和活动列。
查询 ->
SELECT
`s`.`slider_id` as `id`,
`s`.`slider_type` as `type`,
`s`.`slider_name` as `name`,
`sd`.`slider_data_type` AS `sd_type`,
`sd`.`slider_data_value` AS `sd_value`,
`sd`.`slider_data_group` AS `sd_group`,
`sd`.`slider_data_id` AS `sd_id`
FROM `sliders` AS `s`
LEFT OUTER JOIN `slider_data` AS `sd`
ON `s`.`slider_id` = `sd`.`slider_data_s_id`
WHERE
`s`.`slider_id` = '11' AND
( ( `sd`.`slider_data_active` = 1 AND `sd`.`slider_data_delete` =0 ) OR
( `sd`.`slider_data_active` IS NULL AND `sd`.`slider_data_delete` IS NULL ) )
当另一个或 RIGHT 表中没有具有匹配条件的记录时,查询工作正常。但不是什么时候
`sd`.`slider_data_active` = 0 AND `sd`.`slider_data_delete` = 1
当活动列和删除右表中的列为 0 和 1 时,我仍然想从主表或左侧表中检索数据
活动 0 和删除 1 将等于右列中的 NULL
请问有什么解决方法吗?
将WHERE
子句中的AND
条件移动到表连接条件(ON
),当您WHERE
子句中使用LEFT JOINED
表时,它的开始行为类似于INNER JOIN
并且仅在找到完全匹配时才给出结果。
SELECT `s`.`slider_id` as `id`, `s`.`slider_type` as `type`, `s`.`slider_name` as `name`, `sd`.`slider_data_type` AS `sd_type`, `sd`.`slider_data_value` AS `sd_value`, `sd`.`slider_data_group` AS `sd_group`, `sd`.`slider_data_id` AS `sd_id`
FROM `sliders` AS `s`
LEFT OUTER JOIN `slider_data` AS `sd` ON `s`.`slider_id` = `sd`.`slider_data_s_id` AND ( ( `sd`.`slider_data_active` = 1 AND `sd`.`slider_data_delete` =0 ) OR ( `sd`.`slider_data_active` IS NULL AND `sd`.`slider_data_delete` IS NULL ) )
WHERE `s`.`slider_id` = '11'