CODEWITHSUNDEEP
mysql
jimmy
jimmy

Reputation: 314

Another WHERE clause if there is no result

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

Upvotes: 5

Views: 321

Answers (5)

Menelaos
Menelaos

Reputation: 25725

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.

Upvotes: 2

Shawn Balestracci
Shawn Balestracci

Reputation: 7530

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

Upvotes: 7

gbn
gbn

Reputation: 432210

MySQL lacks features to allows this in one go
Examples: DENSE_RANK window function, or TOP..WITH TIES

So, without using a temporary table or IF statements (which require a stored procedure in MySQL anyway), then filter this on the client to the lowest priority value. That is, consume rows until the value changes

SELECT *, 1 AS priority FROM products WHERE language = 1
UNION ALL
SELECT *, 2 AS priority FROM products WHERE language = 2 AND country = 1
UNION ALL
SELECT *, 3 AS priority FROM products WHERE language = 2 AND country = 2
ORDER BY priority;

This does remove roundtrips to the MySQL server, even if not ideal. And it also avoids re-evaluating the previous SELECTs in EXISTS clauses

Upvotes: 1

user359040
user359040

Reputation:

Try:

select p.* 
from (select min(language) minlang, min(country) minctry 
      from products where language = 1 or
                          (language = 2 and country in (1,2)) ) c
join products p 
on p.language = c.minlang and (c.minlang=1 or p.country=c.minctry)

Upvotes: 1

Arioch 'The
Arioch 'The

Reputation: 16045

It seems there is no good way and if possible, you'd better make it into a procedure, either filling the temporary table or returning RowSet object to client

However if nothing that is possible you seem to have limit the secondary and tertiary queries:

SELECT * FROM products WHERE language = 1

UNION ALL

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

UNION ALL

SELECT * FROM products WHERE language = 2 AND country = 2
   AND NOT EXISTS (SELECT * FROM products WHERE language = 1 OR (language = 2 AND country = 1 ))

But that just must be ugly and slow.

Maybe LEFT JOIN with IS NULL would be better than NOT EXISTS though

Upvotes: 1

Related Questions