CODEWITHSUNDEEP
mysql
i Khaltura
i Khaltura

Reputation: 13

mysql select column where condition 1 exists otherwise condition 2 but not both

I have a simple table like this:

id | com_id | sec_id | branch_id | code 
1      1         1        3        some_code_with_all_fields_notnull <<--- this one
2      2         3        3        some_code
3      3         2        6        some_code
4      1       NULL      NULL      some_code_with_some_fields_null <<--- or this one
5      2         3        9        some_code

Now, I need to select ONLY one record as follows:
if sec_id or branch_id is not provided as a parameter, I need to select ONLY the row where the corresponding column value is null. If they have been provided as parameters, I need to select ONLY the row where the corresponding column value matches each of the parameter value.

I tried a plain where clause like this: select code from comcodes where com_id = :com_id and (sec_id = :sec_id or sec_id is null) and (branch_id = :branch_id or branch_id is null)

but where go through all rows and return all rows matching both conditions and that's what I don't want. I want it to return only one row that either meet the first condition or the second not both

The reason I'm using "OR null" part is that I need to return the row with the null value if sec_id or branch_id is provided but no record found that matches them

Can you please help me with this

Upvotes: 1

Views: 204

Answers (1)

fthiella
fthiella

Reputation: 49069

One solution is to add an ORDER BY and then a LIMIT 1:

select code
from comcodes
where
  com_id = :com_id and
  (sec_id = :sec_id or sec_id is null)
  and (branch_id = :branch_id or branch_id is null)
order by
  CASE WHEN (sec_id is not null) THEN 1 ELSE 2 END
LIMIT 1

this will give priority to the sec_id not null, if present.

Upvotes: 2

Related Questions