CODEWITHSUNDEEP
phpmysqlsqlselect
Vivaan Mathur
Vivaan Mathur

Reputation: 33

Run second query if first returns nothing, if first returns row then return that row

I am trying to run this MySQL query from PHP app.

SELECT * FROM LIVE WHERE STATUS=1 AND (CRITERIA='demo' OR CRITERIA='all' OR CRITERIA='1' OR CRITERIA='1E')

Which is working perfectly. However I want that if this query returns nothing then it should execute another query somewhat like this.

SELECT * FROM LIVE WHERE STATUS=0 AND (CRITERIA='demo' OR CRITERIA='all' OR CRITERIA='1' OR CRITERIA='1E')

I have tried multiple things like SELECT IF or SELECT IFNULL but none of them seems to work. What am I doing wrong? Basically I want that if the first query returns row then give that but if not then run the second query.

Now I saw this question on stackoverflow - Second SELECT query if first SELECT returns 0 rows . I tried it but in return i got this error Unrecognized statement type. (near "IF" at position 0)

Upvotes: 0

Views: 951

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521674

I would use the following logic:

SELECT *
FROM LIVE
WHERE CRITERIA IN ('demo', 'all', '1', '1E') AND
      (STATUS = 1 OR
       STATUS = 0 AND NOT EXISTS (SELECT 1 FROM LIVE
                                  WHERE CRITERIA IN ('demo', 'all', '1', '1E') AND
                                        STATUS = 1));

To see how the above logic works, should there be STATUS = 1 records, they would all be selected, and no STATUS = 0 records would be included due to the exists logic. In the case where no records match STATUS = 1, then the STATUS = 0 records would be included, again due to the exists logic.

Upvotes: 1

Akina
Akina

Reputation: 42686

If 1st query may return strictly 1 or 0 rows (not more) than simply

SELECT * 
FROM live 
WHERE status IN (1, 0) 
AND criteria IN ('demo', 'all', '1', '1E')
ORDER BY status DESC 
LIMIT 1

If 1st query may return more than 1 row then the optimal solution depends on MySQL version.

Upvotes: 2

Related Questions