CODEWITHSUNDEEP
mysqlsql
developarvin
developarvin

Reputation: 5069

Mysql -- Use a secondary subquery if and only if the first subquery returned empty

Given two subquery tables t1 and t2, how can I return t2 if and only if t1 returned empty rows?

Edit: Added example

T1

SELECT * 
  FROM common_table 
 WHERE language_id = 1

T2

SELECT * 
  FROM common_table 
 WHERE language_id = 2

Basically what I am doing is that in case the T1 return empty rows, I would like it to execute T2 and return those rows. Now, I am fully aware that I can do this in PHP but the query is a subquery and I would rather let SQL (not PHP) code handle it.

Upvotes: 4

Views: 4101

Answers (4)

onedaywhen
onedaywhen

Reputation: 57063

SELECT * 
  FROM common_table 
 WHERE language_id = 1
UNION
SELECT * 
  FROM common_table 
 WHERE language_id = 2
       AND NOT EXISTS (
                       SELECT * 
                         FROM common_table AS T2 
                        WHERE T2.language_id = 1
                      );

Upvotes: 0

Thomas
Thomas

Reputation: 64655

Select ...
From common_table
Where language_id = 1
    Or  (
        language_id = 2
        And Not Exists  (
                        Select 1
                        From common_table
                        Where language_id = 1
                        )
        )

Upvotes: 3

Mahesh
Mahesh

Reputation: 34625

My MySql knowledge is a bit rusty and I think this should work.

SELECT * FROM common_table 
         WHERE language_id = 2 and 
               0 = ( SELECT count(*) FROM common_table 
                                     WHERE language_id = 1
                   ) ;

Upvotes: 1

John Petrak
John Petrak

Reputation: 2928

In MSSQL I can do something like 

If EXISTS (SELECT * FROM common_table WHERE language_id = 1)
BEGIN
    SELECT * FROM common_table WHERE language_id = 1
END ELSE BEGIN
    SELECT * FROM common_table WHERE language_id = 2
END

Should be the same in mySQL or similar

Upvotes: 2

Related Questions