Fetch data using mysql left join using where clause on both table

1884 views mysql
5

I have 2 tables, custom_leads_fields and custom_leads_fields_option. The primary key of custom_leads_fields is saved in custom_leads_fields_option table as c_id.

I need to fetch all the records from custom_leads_fields table where status = 1 and then I also needs matching records from custom_leads_fields_option where status = 1

custom_leads_fields table
c_id | user_id | label | status | 
1    | 591     |   A   |   1    |
2    | 591     |   B   |   1    |
3    | 591     |   C   |   0    |

custom_leads_fields_option table
id | c_id                   | option | status 
1  |    2                   |   yes  |   1
2  |    2                   |   no   |   1
3  |    2                   |   may  |   0
4  |    3                   |   yy   |   1
5  |    3                   |   zz   |   1

Output required:

c_id   |  label  |   option
1      |    A    |     
2      |    B    |     yes
2      |    B    |     no

It should return records from first table if status = 1 even if records are not available in second table, but if records are available in second table then only those records should be fetched whose status = 1 I have written a query but it does not return label 'A' because matching records are not available in other table.

SELECT 
`custom_leads_fields`.`c_id` AS `field_id`, 
`custom_leads_fields_option`.`id` AS `option_id`, 
`custom_leads_fields`.`label`, 
`custom_leads_fields_option`.`option` 
FROM 
`custom_leads_fields` 
LEFT JOIN 
`custom_leads_fields_option` 
ON custom_leads_fields.id = custom_leads_fields_option.custom_leads_field_id 
WHERE 
(`custom_leads_fields`.`user_id`=591) 
AND (`custom_leads_fields`.`status`=1) 
AND (`custom_leads_fields_option`.`status`= 1) 

answered question

what's the error?

it should also return lable A even if matching records are not available in other table

Put your where in join condition more like custom_leads_fields.id = custom_leads_fields_option.custom_leads_field_id and custom_leads_fields.status = 1 and custom_leads_fields_option.status = 1

on the join criteria can you change the query to map to the correct names of the id columns you've used in the sample tables.

1 Answer

8

you need to move status condition to join condition , this will give your desired output. All the records from left table and matched record (with status =1 condition) from right table

SELECT 
`custom_leads_fields`.`c_id` AS `field_id`, 
`custom_leads_fields_option`.`id` AS `option_id`, 
`custom_leads_fields`.`label`, 
`custom_leads_fields_option`.`option` 
FROM 
`custom_leads_fields` 
LEFT JOIN 
`custom_leads_fields_option` 
ON custom_leads_fields.id = custom_leads_fields_option.custom_leads_field_id 
AND (`custom_leads_fields_option`.`status`= 1) // changed line
// ^^^^^^^^^^^^^^^^^^
WHERE 
(`custom_leads_fields`.`user_id`=591) 
AND (`custom_leads_fields`.`status`=1) 

posted this

Have an answer?

JD

Please login first before posting an answer.