SQL LEFT JOIN with where clause from another column

user_2006699 0
Viewed 1517

I am trying to join two columns and where condition is based on Delete and Active column in right table.

Query ->

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 ) )

Query is working fine when there is no record in another or RIGHT table with matching condition. But not when 

`sd`.`slider_data_active` = 0 AND `sd`.`slider_data_delete` = 1

I want to still retrieve data from main or left table when active column and delete column in right table is 0 and 1

Active 0 and delete 1 will be equal to NULL in right column

any workaround please?

outer-join left-join mysql sql database
1 Answers

Move AND Condition in WHERE clause to table joining condition (ON), When you use LEFT JOINED table in WHERE clause then its start behaving like INNER JOIN and only give result when there is exact match found. 

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'
user_49649230
Related