2

I need some help with a query as I don't seem to get my head around it.

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?

Thx!

Filip Huysmans
  • 1,301
  • 1
  • 20
  • 44

1 Answers1

2

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)
Nigel Ren
  • 56,122
  • 11
  • 43
  • 55
  • AND ( `vl`.`label_id` NOT IN ('10') **AND** `vl`.`label_id` IS **NOT** NULL) ? Now you select when there is no label_id ? – splash58 Aug 05 '19 at 14:57
  • @splash58 I thought *It leaves out however all vacancies that have no records* means they want the records when it is null. – Nigel Ren Aug 05 '19 at 15:00
  • I think OP want records having label. Else, you are right – splash58 Aug 05 '19 at 15:03
  • Nigel is correct in his thinking, not all vacancies have labels but my code skips them because like he explained with the nulls – Filip Huysmans Aug 05 '19 at 15:04