CODEWITHSUNDEEP
mysqlmariadb
Adam Baranyai
Adam Baranyai

Reputation: 3867

MySQL - query condition, which only triggers if another query condition returns no results

Is it possible in MySQL, to add a WHERE condition, only if a preceeding where condition would return no results, without firing two consecutive queries?

So, basically what I am looking for is:

Consider the following table: Currencies:

| CurrencyId | CurrencyCode | CurrencyName         |
----------------------------------------------------
| 1          | EUR          | Euro                 |
| 2          | USD          | United States Dollar |
| 3          | GBP          | Great Brittain Pond  |
| 4          | RON          | Romanian New Lei     |
----------------------------------------------------

Consider the following query: 

SELECT * FROM Currencies WHERE (CurrencyCode LIKE '%RO%' OR CurrencyName LIKE '%RO%') AND CurrencyId <= 4

This query will return row 1 (Euro contains 'ro'), and row 4 (RON contains 'RO'). What I would like to achieve, is to write a query, which first runs only the first LIKE statement, if there are results, it ignores the second LIKE statement, otherwise, it tries the second LIKE statements. In both cases the query should run the other conditions. So basically, the above query, if rewritten, should only return row number 4.

Is this possible somehow in MySQL?

Upvotes: 0

Views: 81

Answers (1)

Rick James
Rick James

Reputation: 142296

Get both rows, then pick the preferred one:

SELECT ...
    FROM ...
    WHERE ( ... LIKE ... OR ... LIKE ... )
      AND ...
    ORDER BY CurrencyCode LIKE '%RO%' DESC
    LIMIT 1;

LIKE returns true/false, which can be treated as 1/0, hence the DESC.

If there are multiple matching codes, they will be returned first, but in no particular order.

If you want to give preference to rows where the Code and Name match, then

    ORDER BY CurrencyCode LIKE '%RO%' DESC,
             CurrencyName LIKE '%RO%' DESC

(See also CASE ... for variations on this.)

Modified

Based on new specification...

( SELECT ... WHERE CurrencyCode LIKE ... )
UNION ALL
( SELECT ... WHERE CurrencyName LIKE ...
               AND NOT EXISTS ( SELECT 1
                        WHERE CurrencyCode LIKE ... ) )

The first SELECT will get all the matching Codes, if any.
The second SELECT will fail if there are any matching Codes, otherwise will deliver matching Names.

You could add ORDER BY in each SELECT or at the very end.

Upvotes: 1

Related Questions