SQL WHERE with conditions but no CASE-statement

Question

I have to work with a given database.

I found this article, but did not succeed in adapting it for my needs:

http://weblogs.sqlteam.com/jeffs/archive/2003/11/14/513.aspx

What I want to do

I work with MYSQL 5 and PHP 5.4.

The database has localized entries for the same subject as single rows.

Structure of the table

+---+--------+------------+--------+----+-----+  
|id |a       |b           |c       |d   |e    |  
+---+--------+------------+--------+----+-----+  
|1  |aaa     |German text |booked  |0   |1    |  
+---+--------+------------+--------+----+-----+  
|2  |aaa     |English text|booked  |1   |0.25 |  
+---+--------+------------+--------+----+-----+  
|3  |aaa     |French text |booked  |2   |1    |  
+---+--------+------------+--------+----+-----+  
|4  |bbb     |German text |free    |0   |-0.25|  
+---+--------+------------+--------+----+-----+  
|5  |bbb     |French text |booked  |2   |1    |  
+---+--------+------------+--------+----+-----+  
|6  |ccc     |German text |free    |0   |-0.25|  
+---+--------+------------+--------+----+-----+  
|7  |ccc     |English text|free    |1   |0.5  |  
+---+--------+------------+--------+----+-----+  
|8  |ddd     |German text |free    |0   |-0.25|  
+---+--------+------------+--------+----+-----+  
|9  |ddd     |Russian text|booked  |5   |0.9  |  
+---+--------+------------+--------+----+-----+  
|10 |eee     |Italian text|free    |4   |1.2  |  
+---+--------+------------+--------+----+-----+  
|11 |eee     |English text|free    |1   |0.3  |  
+---+--------+------------+--------+----+-----+  

Columns:

a = subject  
b = localized text  
c = value  
d = language-key  
e = value  

I have up to three different language-values to check for column "d":

• request_value (first choice, if TRUE take this row of subject1)
• fallback_value (if request_value failed check this, if TRUE take this row of subject1)
• default_value (if request_value and fallback_value failed check this, if TRUE take this row of subject1)
• if all three checks failed no row for subject1 is returned.

There should be returned only one row of all subject1-entries or none.

Examples for requests

request_value = 0 (German)  
fallback_value = 1 (English)  
default_value = 0 (German)  

should return rows id = 1, 4, 6, 8, 11  


request_value = 1 (English)  
fallback_value = 1 (English)  
default_value = 0 (German)  

should return rows id = 2, 4, 7, 8, 11  


request_value = 5 (Russian)  
fallback_value = 1 (English)  
default_value = 0 (German)  

should return rows id = 2, 4, 7, 9, 11  

I thought of a condition in the WHERE-statement and want to avoid the CASE-statement.

Query (prototyping):

SELECT a, b  
FROM table  
WHERE  
c = value1  
AND (check conditions for d)  
AND e > 0  

Please, does anyone can give me the WHERE-statement, even with a CASE-statement if this is unavoidable?

Is there a better (or faster) way to filter the table?

Thank you in advance.

Answer 1

What you are trying to do is very different from what the article does: the article teaches how to rewrite conditionals on a single row without the case statement; your task, on the other hand, needs conditions that must work across multiple rows. In other words, article's conditions are horizontal, while you are looking for a vertical one.

One way to build a query that picks a single row is using COALESCE. Assuming that table has a primary key called id, you can do it like this:

-- Change these values as needed
SET @request_value = 5;
SET @fallback_value = 1;
SET @default_value = 0;

SELECT id
FROM test a
WHERE id = COALESCE (
  (SELECT ID FROM test b WHERE a.a=b.a AND d=@request_value)
, (SELECT ID FROM test b WHERE a.a=b.a AND d=@fallback_value)
, (SELECT ID FROM test b WHERE a.a=b.a AND d=@default_value)
)

Here is a demo on sqlfiddle.

Note that in order for this to work, each individual SELECT must return at most a single row. In your case, there must be no more than one row for any combination of {a, d}.