phpcodeigniternullleft-joinwhere-in

CodeIgniter SELECT query with LEFT JOIN, IN() with possible NULL values and GROUP BY

I need to join two tables for my CodeIgniter application.

First table vacancies:

vac_id
vac_title
vac_location
vac_description
is_deleted
status

Second table vacancies_labels:

vac_id
Label_id

Now I would like to get an output containing all vacancies within a certain location but they also cannot contain the label_id '10' nonetheless of the location.

SELECT `v`.*
FROM `vacancies` AS `v`
LEFT JOIN `vacancies_labels` as `vl` ON `v`.`vacancy_id` = `bl`.`vacancy_id`
WHERE `v`.`vac_location` = 'russia'
AND `v`.`is_deleted` != 1
AND `v`.`status` = 1
AND `vl`.`label_id` NOT IN ('10')
GROUP BY `v`.`vacancy_id`

This results only in the vacancies that have a record in the vacancies_labels table that are not 10. It leaves out however all vacancies that have no records at all in the vacancies_labels table but fit within the location range.

What am I missing here?

Solution

  • Using a LEFT JOIN, if the record is not found, then the values will return null. But in your WHERE clause, you have 

    AND `vl`.`label_id` NOT IN ('10')

    as NOT IN doesn't consider nulls you have to do something like...

    AND ( `vl`.`label_id` NOT IN ('10') OR `vl`.`label_id` IS NULL)