CODEWITHSUNDEEP
mysqlcontrol-flow
nevvermind
nevvermind

Reputation: 3392

Another SELECT if the first SELECT returned an empty set

This is my table:

  id    id_1     camp 
 1        0         x1     
 2        0         x2
 3        0         x3
 4        1         x4
 5        1         x5
 6        1         x6
 7        3         x7
 8        3         x8
 9        3         x9

I am in need of a SQL that, if there are no rows with id_1 of "x", it should return those with id_1 of 0. For example, if I were to select id_1 = 2, it would return the select * whereid_1= 0.

Could this be done in one statement? I had little luck using IF EXISTS or CASE. Syntax...

Thanks, as always

Upvotes: 3

Views: 4654

Answers (4)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115630

  SELECT * 
  FROM mytable 
  WHERE id_1 = 2 
UNION ALL
  SELECT * 
  FROM mytable 
  WHERE id_1 = 0 
    AND NOT EXISTS 
        ( SELECT * 
          FROM mytable 
          WHERE id_1 = 2
        )

Upvotes: 2

Martin Smith
Martin Smith

Reputation: 453658

SELECT SQL_CALC_FOUND_ROWS id, id_1, camp
FROM your_table
WHERE id_1 = 2

UNION ALL

SELECT id, id_1, camp
FROM your_table
WHERE 
FOUND_ROWS() = 0 AND id_1 = 0;

Upvotes: 2

pavanred
pavanred

Reputation: 13823

If there are no rows with id_1 of "x", it should return those with id_1 of 0. You can try this - 

IF EXISTS ( SELECT * FROM mytable WHERE id_1 = 2 ) 
    SELECT * FROM mytable WHERE id_1 = 2 
ELSE
    SELECT * FROM mytable WHERE id_1 = 0

Upvotes: -3

Lavir the Whiolet
Lavir the Whiolet

Reputation: 1016

Don't ignore a way to implement this logic in your code instead of SQL.

Upvotes: 0

Related Questions