Geek asks and answers

Another WHERE clause if no result

What is the best way to do this in a single 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
+5
source share
5 answers

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

The original (without the latter and does not exist) had a flaw.

Turns out it's not as smart as I thought. See comments below. It does not work if the first and third queries return data. It works if you have only one union, but not two or more.

In mysql is pretty easy:

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 a discussion of found_constructions () and SQL_CALC_FOUND_ROWS here: http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows

+7
source

- : ( EXISTS LIMIT ).

(
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)
)

count (*), NULL.

+2

MySQL , : DENSE_RANK , TOP..WITH TIES

, IF- ( MySQL), . , ,

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;

MySQL, . SELECT EXISTS

+1

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)
+1

There seems to be no good way, and if possible, you need to do this in the procedure, either by filling out a temporary table or returning a RowSet object to the client

However, if nothing is impossible, it seems that you have a restriction on 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 it just has to be ugly and slow.

LEFT JOIN with IS NULL may be better than NOT EXISTS, though

+1
source

More articles:


All Articles