SQL WHERE condition when one does not return true, then try other-CodePudding

I have to query a table based on two fields such that if first field matches then don't check the second but if first field does not match then check if second field matches for a value

something like:

SELECT * FROM table
WHERE cart_id=389 OR (cart_id IS NULL AND user_id=26)

But if first condition succeeds, it must not check for second condition

Example:

Suppose the following is my table

id | cart_id | user_id
1  | 389     | 26
2  | null    | 26
3  | 878     | 26

on querying for cart_id = 389 and user_id = 26, I should get back only record 1 and NOT 2
on querying for cart_id = 1 and user_id = 26, I should get back only records 2 and NOT 1 and 3

CodePudding user response：

The only way I can think of, is to do this in two steps and check the result of the first step in the second:

with the_cart as (
   SELECT * 
   FROM the_table
   WHERE cart_id=389 
)
select *
from the_cart
union all
select *
from the_table
where cart_id IS NULL 
  AND user_id=26
  and not exists (select * from the_cart);

If the first query (using cart_id=389) returns something the second query from the union will not be run (or more precisely return no rows) due to the not exists() condition.

Online example

CodePudding user response：

Based on your updated example data, your where clause would be:

WHERE cart_id = 389 and user_id = 26

but given how trivial that is, it’s difficult to believe that’s really what you’ve been asking all along.

=== Updated based on latest example…

WHERE (cart_id = 389 and user_id = 26)
OR (cart_id is null and user_id = 26)