CODEWITHSUNDEEP
mysql
Headshota
Headshota

Reputation: 21449

MySQL Select another row if one doesn't exist

I need to select a row from the table with the specific id, but if the row doesn't exist I need to select the first row. is it possible to do it in a single query?

example: I have a table with an id and text fields. and I have 3 rows in the table

id    txt
----------------
1     text1
2     text2
3     text3

I need to select the first row if say SELECT * FROM myTable WHERE id = 4 doesn't exist Else select the row with id 4.

Upvotes: 10

Views: 3444

Answers (3)

fede72bari
fede72bari

Reputation: 543

Here there is another way that may be considered

SELECT * FROM table1  
WHERE [criteria 1] AND [criteria 2]  

UNION 

SELECT * FROM table1  
WHERE [criteria 1] AND NOT EXISTS 
(
   SELECT * FROM table1  
   WHERE [criteria 1] AND [criteria 2]    
)

LIMIT 1;

Upvotes: 0

Aziz
Aziz

Reputation: 20705

SELECT * FROM (
   SELECT * FROM table1 WHERE [..your criteria...] LIMIT 1
   UNION
   SELECT * FROM table1 LIMIT 1    
) LIMIT 1

Upvotes: 3

Marco
Marco

Reputation: 57573

Try this:

SELECT * FROM 
   (SELECT * FROM your_table
      WHERE id = your_id
      LIMIT 1
    UNION
    SELECT * FROM your_table
      LIMIT 1) a
LIMIT 1

The idea is to take first desired row and appending to this very first row, finally taking first one. If desired row does not exists, first one will be selected...

Upvotes: 12

Related Questions