Another WHERE clause if there is no result

Question

What is the best way to do this in one query in mysql?

SELECT * FROM products WHERE language = 1

if there is no result

SELECT * FROM products WHERE language = 2 AND country = 1

if there is no result

SELECT * FROM products WHERE language = 2 AND country = 2

Answer 1

I've edited it to make it work, but it is no longer elegant.

---

The original (without the last and not exists) had a flaw.

Turns out this isn't as clever as I thought. See comments below. It fails if the first and third queries return data. It does work if you have only one union, but not two or more.

---

It's quite easy in mysql:

select SQL_CALC_FOUND_ROWS * from products where language = 1
union
SELECT * FROM products WHERE language = 2 AND country = 1 and found_rows()=0
union
SELECT * FROM products WHERE language = 2 AND country = 2 and found_rows()=0 
AND not exists(select * from products where language = 1)

See the discussion of found_rows() and SQL_CALC_FOUND_ROWS here: http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows

Answer 2

You could use something like the following: (edited to use EXISTS and LIMIT based on comments).

(
SELECT * FROM products WHERE language = 1
)
UNION
(
SELECT * FROM products WHERE language = 2 AND country = 1
AND NOT EXISTS(SELECT count(*) FROM products WHERE language = 1 limit 1)
)
UNION
(
SELECT * FROM products WHERE language = 2 AND country = 2 
AND NOT EXISTS(SELECT count(*) FROM products WHERE language = 2 AND country = 1 limit 1)
AND NOT EXISTS(SELECT count(*) FROM products WHERE language = 1 limit 1)
)

You check with nested queries and count(*) that previous queries were NULL.