CODEWITHSUNDEEP
mysqlsqldatabaseleft-joinouter-join
Lovepreet Singh Batth
Lovepreet Singh Batth

Reputation: 389

SQL LEFT JOIN with where clause from another column

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?

Upvotes: 2

Views: 2478

Answers (1)

Jaydip Jadhav
Jaydip Jadhav

Reputation: 12309

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'

Upvotes: 4

Related Questions