MySQL SELECT WHERE IN LIST and NOT IN LIST in the same SQL

Question

I have this:

    $ids = "1,2,3,4,5";
    $sqlQuery = "SELECT id, moderation_date
                    FROM table_live
                    WHERE id IN (".$ids.")";

    $q = $this->CI->db->query($sqlQuery);

    if($q->num_rows() > 0) {
        foreach ($q->result() as $row) {
            $arr[] = $row;
        }
    }

    return $arr;

This is just working fine if all ids exist in table_live and return

           array([id] => 1 [moderation_date] => 2012-04-11 12:55:57)....

The problem: If I send a list of ids 1-2-3-4-5 where only 1-2-5 match the IN LIST clause I need to return all in list and for those don't match the list a null value.

           array([id] => 3 [moderation_date] => null) 

Solution

generate an outer join statement so that you get:

SELECT ids.id, table_live.moderation_date
FROM (select 1 id union all select 2 union all ....) ids
LEFT JOIN table_live
ON ids.id = table_live.id

where ids is a subquery enumerating all the values, something like this:

$ids = '1,2,3,4,5'
$subquery = 'select '.str_replace(',', ' id union all select ', $ids).''
$sql = "SELECT ids.id, table_live.moderation_date
FROM ($subquery) ids
LEFT JOIN table_live
ON ids.id = table_live.id"

be sure to select ids.id, not table_live.id. That way, the ids will always show up, and the moderation_date only if the corresponding row exists in table_live.

Another approach would be to keep the query as you had it, store the result in an array, and then merge the arrays in php so that you retain all keys, and fill in the values only where the key matches in both arrays.

I am not really sure what kind of db library you're using so I don't know how to obtain an array of the resultset, but suppose you would have stored the rows in a php array, using a string representation of the id as key, and the date as value, then this code should do the trick:

$items = array(
    '1' => NULL
,   '2' => NULL
,   ...
); 
//note: use string keys in order to merge!!
$result = array_merge($items, $resultset);

see: http://php.net/manual/en/function.array-merge.php

Comments

[D'Arcy Rittich]

Please provide sample data and desired output.

[hjpotter92]

if all non-existing ids have moderation_date as NULL, you can use it while assigning the $arr[]

[Adrian P.]

Thanks, Roland. I love your approach but I had an SQL error: Unknown column 'ids.id' in 'field list' SELECT ids.id, table_live.moderation_date FROM (SELECT 1-2-4-5-11-26-31) ids LEFT JOIN table_live ON ids.id = table_live.id $ids is a string not a table

[Roland Bouman]

right, problem was that the generated subquery did not have a column alias. Fixed now.

[Adrian P.]

You're a genius. Thanks a lot!

[Adrian P.]

Just a small problem: if ids string has only one value the str_replace will not working so it will break the SQL query

[Adrian P.]

And the solution: $pos = strpos($ids, ','); if ($pos === false) { $subQuery = "SELECT ".$ids." id "; }else{ $subQuery = "SELECT ".str_replace('-', ' id UNION ALL SELECT ', $ids).""; } Thank you