0

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)

Vivaan Mathur
  • 33
  • 7
  • 1
    I think you should use `CASE` in the `SELECT` but currently its a bit hard to visualize what you're trying to achieve. If you have sample data, results of your current query and your expected result, it would be much easier to help you. – FanoFN Oct 22 '20 at 05:33

2 Answers2

2

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.

Akina
  • 39,301
  • 5
  • 14
  • 25
  • Thank you. So this code will prefer status 1 over 0 right? I mean I wish to show the running meetings first. – Vivaan Mathur Oct 22 '20 at 16:34
  • @VivaanMathur *So this code will prefer status 1 over 0 right?* Depends on ORDER BY expression. – Akina Oct 22 '20 at 16:43
  • Although this works as intended, I would use: `ORDER BY status = 1 DESC `, which points to the preferred value and could be changed to any other value. – forpas Oct 23 '20 at 06:32
  • @forpas When `WHERE status IN (1, 0)` is specified then `ORDER BY status = 1 DESC` simply adds excess compare without any profit... – Akina Oct 23 '20 at 06:46
  • This is why I said *this works as intended*. My point is that using `ORDER BY status = 1 DESC` adds to readability and scalability. – forpas Oct 23 '20 at 06:48
1

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.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360