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

Question

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?

Relevant reading: Why MYSQL IN keyword not considering NULL values and AND field NOT IN(NULL) returns an empty set and NOT IN clause and NULL value and mysql NULL value in where in CLAUSE and MySQL "NOT IN" query not working — mickmackusa
– mickmackusa ♦, Commented Apr 14 at 20:17

Nigel Ren · Accepted Answer · 2019-08-05 14:55:34Z

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)

answered Aug 5, 2019 at 14:55

Nigel Ren

57.3k11 gold badges49 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

splash58 Over a year ago

AND ( vl.label_id NOT IN ('10') AND vl.label_id IS NOT NULL) ? Now you select when there is no label_id ?

Nigel Ren Over a year ago

@splash58 I thought It leaves out however all vacancies that have no records means they want the records when it is null.

splash58 Over a year ago

I think OP want records having label. Else, you are right

Filip Huysmans Over a year ago

Nigel is correct in his thinking, not all vacancies have labels but my code skips them because like he explained with the nulls

mickmackusa · Accepted Answer · 2025-04-14 22:15:45Z

Think about your task this way...

You are GROUPing BY vacancy_id which means that you are mitigating a one-to-many relationship between the vacancies and their labels.
Imagine you have a single vacancy record with three labels which represent exclusion rules. Label 2 means "no smoking allowed", label 5 means "no dogs allowed", and label 10 means "no Americans allowed". If you want to ensure an American-free result set, you cannot simply write != 10 in the LEFT JOIN's ON clause and you cannot write != 10 in the WHERE clause because then you would get two (pre-grouping) rows for the vacancy (one with label 2 and another with label 5).
Long story short, you need to use HAVING after grouping to properly filter out vacancies with the blacklisted label_id.

SQL: (SQLize Demo)

SELECT `vacancies`.*
FROM `vacancies`
LEFT JOIN `vacancies_labels` USING (`vacancy_id`)
WHERE `vac_location` = 'russia'
AND `is_deleted` != 1
AND `status` = 1
GROUP BY `vacancy_id`
HAVING COALESCE(SUM(label_id = 10), 0) 0

CodeIgniter method which can receive a label blacklist array of zero or more label ids:

public function get(string $location, array $excludeLabelIds = []): array
{
    return array_reduce(
        $excludeLabelIds,
        fn($db, $labelId) => $db->having(
            sprintf(
                'COALESCE(SUM(%s = %d), 0) = 0',
                $db->escape_identifiers('label_id'),
                $db->escape($labelId)
            )
        ),
        $this->db
    )
    ->select('vacancies.*')
    ->from('vacancies')
    ->join('vacancies_labels', 'vacancy_id', 'left')
    ->where([
        'vac_location' => $location,
        'is_deleted !=' => 1,
        'status' => 1,
    ])
    ->group_by('vacancy_id')
    ->get()
    ->result();
}

Collectives™ on Stack Overflow

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

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related