MySQL - selection of results with the specified ID or with zero

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 the line where adv_id = 22 and user_id = 3 . If this line does not exist, I want to get the line where adv_id = 22 and user_id is null .

I tried this way:

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

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

How can I do this in a single request? 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

Explanation: the subquery that I generated as t2 aggregates by ADV_ID and displays the value 3 if this value occurs in one or more records, otherwise it returns NULL . Then we attach this subquery to the original table, provided that both USER_ID NULL , or, if not, match two USER_ID values.

You can change the demo to see that it generates the output you want for other inputs.

Answer 2

 SELECT * FROM test WHERE ADV_ID IS NOT NULL AND USER_ID IS NOT NULL UNION ALL SELECT * FROM test WHERE USER_ID IS NULL AND NOT EXISTS ( SELECT 1 FROM test WHERE ADV_ID IS NOT NULL AND USER_ID IS NOT NULL ) 

Select all rows with the first condition: ADV_ID IS NOT NULL AND USER_ID IS NOT NULL

and then UNION ALL with the same table if the first condition is NOT EXISTS .

Thus, we only get results if the first condition does not return any rows.

The MySQL UNION ALL statement is used to combine result sets of 2 or more SELECT statements.

Answer 3

try like this:

 SELECT * FROM `table` t1 WHERE (t1.adv_id = 44) AND ((t1.user_id = 3) OR (NOT EXISTS (select * from `table` t2 where t2.adv_id=t1.adv_id and t2.user_id = 3) AND t1.user_id is null )) 

Demo