MySQL - Select results with specified ID or with null

Question

I have one table:

| ID | ADV_ID | USER_ID |
| 1  |   22   |   NULL  |
| 2  |   22   |    3    |
| 5  |   44   |   NULL  |

and now, I want to select row where adv_id = 22 and user_id = 3. If that row doesn't exist, I want to get row where adv_id = 22 and user_id is null.

I tried in that way:

SELECT * FROM `table` WHERE adv_id = 22 AND (user_id = 3 OR user_id is null)

but this query return two rows - with user_id = NULL and with user_id = 3. I want to get one row - with user_id = 3 or (if not exist), with user_id = NULL.

How I can do it in one query? Thanks.

Answer 1

Use conditional aggregation:

SELECT t1.*
FROM yourTable t1
INNER JOIN
(
    SELECT
        ADV_ID,
        CASE WHEN COUNT(CASE WHEN USER_ID = 3 THEN 1 END) > 0 THEN 3 END USER_ID
    FROM yourTable
) t2
    ON t1.ADV_ID = t2.ADV_ID AND
       ((t1.USER_ID IS NULL AND t2.USER_ID IS NULL) OR (t1.USER_ID = t2.USER_ID))
WHERE
    t1.ADV_ID = 22;

Demo

For an explanation, the subquery I have aliased as t2 aggregates over the ADV_ID, and outputs the value 3 if that value occurs in one or more records, otherwise it outputs NULL. Then, we join this subquery back to your original table on the condition that both USER_ID values are NULL, or, if not, that the two USER_ID values match.

You may modify the demo to see that it generates the output you want for other inputs.