SQL IF ELSE / CASE clause in WHERE condition

Question

I have a very large MySQL statement looped through php foreach and each loop connected to the previous with union all. I will simplify the statement to the core of my problem, if needed I can of course also add more details later on request.

I have this table

+--------+-----------+-----------+
|   ID   |   LANG    |   TITLE   |
+--------+-----------+-----------+
|   1    |    EN     |   T-A     |
+--------+-----------+-----------+
|   1    |    FR     |   T-A     |
+--------+-----------+-----------+
|   2    |    FR     |   T-B     |
+--------+-----------+-----------+
|   3    |    DE     |   T-C     |
+--------+-----------+-----------+
|   3    |    EN     |   T-C     |
+--------+-----------+-----------+

I want to write a WHERE condition in the SQL SELECT that should show me for each ID maximum one result. But it should show results only if LANG is FR or EN. On top FR should be prefered and EN should only be displayed as alternative if no FR is available for the ID. So the result would look like this.

+--------+-----------+-----------+
|   ID   |   LANG    |   TITLE   |
+--------+-----------+-----------+
|   1    |    FR     |   T-A     |
+--------+-----------+-----------+
|   2    |    FR     |   T-B     |
+--------+-----------+-----------+
|   3    |    EN     |   T-C     |
+--------+-----------+-----------+

I have tried to build something by myself with IF - ELSE / CASE but I am not very experienced with SQL so any help would be much appreaciated.

A simplified SQL I tried would be something like

SELECT * FROM `table` 
WHERE `table`.`ID` = 1
IF `table`.`LANG` = 'FR' 
BEGIN
  AND `table`.`LANG` = 'FR' 
END
ELSE
BEGIN
  AND `table`.`LANG` = 'EN' 
END

union all 
SELECT * FROM `table` 
WHERE `table`.`ID` = 2
IF `table`.`LANG` = 'FR' 
BEGIN
  AND `table`.`LANG` = 'FR' 
END
ELSE
BEGIN
  AND `table`.`LANG` = 'EN' 
END

union all 
SELECT * FROM `table` 
WHERE `table`.`ID` = 3
IF `table`.`LANG` = 'FR' 
BEGIN
  AND `table`.`LANG` = 'FR' 
END
ELSE
BEGIN
  AND `table`.`LANG` = 'EN' 
END

Sitenote I may not use any construct with ORDER BY combined with LIMIT 1 since I am looping the SQL through a php for each loop multiple times.

EDIT: SOLUTION that worked for me

SELECT * FROM `table1`
WHERE ID = 1
AND lang = 'FR'
OR (lang = 'EN' AND ID NOT IN (SELECT ID FROM table1 WHERE lang = 'FR'))

I wonder, is this for some tutorial or online course? I've seen strikingly similar questions in the past. — ypercubeᵀᴹ, Sep 06 '13 at 13:23

Janick Bernet · Accepted Answer · 2013-09-06T13:58:00.890

1

The most elegant and efficient solution with a sophisticated query optimizer would be this:

SELECT * FROM (
    SELECT * FROM `table`
    WHERE ID IN (
      SELECT id FROM `table` 
      WHERE lang = 'EN'
      EXCEPT
      SELECT id FROM `table`
      WHERE lang = 'FR'
    ) OR table.LANG ='FR'
) t1
WHERE id = ?

This gives you the desired result, filtered by ID. In case the optimizer is however not able to push down the id = ? you might have to do it yourself to get decent performance:

  SELECT * FROM `table`
    WHERE id = ? AND (ID IN (
      SELECT id FROM `table` 
      WHERE lang = 'EN' AND ID = ?
      EXCEPT
      SELECT id FROM `table`
      WHERE lang = 'FR' AND ID = ?
    ) OR table.LANG ='FR')

However, if you can, I would get all the results at once and not iterate over the IDs in the first place:

SELECT * FROM `table`
WHERE ID IN (
  SELECT id FROM `table` 
  WHERE lang = 'EN'
  EXCEPT
  SELECT id FROM `table`
  WHERE lang = 'FR'
) OR table.LANG ='FR'

This will get you all the IDs that have lang 'EN' and no corresponding 'FR' plus all the 'FR's. Alternatively you could also try:

SELECT * FROM `table`
WHERE lang = 'FR'
OR (lang = 'EN' AND ID NOT IN (SELECT ID FROM table WHERE lang = 'FR'))

or

SELECT * FROM `table`
WHERE lang = 'FR'
OR (table.LANG = 'EN' AND NOT EXISTS (SELECT * FROM table t1 WHERE lang = 'FR' AND t1.id = table.id))

But my guess would be the first query is fastest.

edited Sep 06 '13 at 13:58

answered Sep 06 '13 at 13:24

Janick Bernet

20,544
2
29
55

OP is asking for a query that returns only 1 row per ID. This query will not do that, as your outer query may return multiple rows for each ID in the inner query. – Dan Sep 06 '13 at 13:26
Ok, that was very not clear, since his example table consistent of multiple rows. But obviously this can just be wrapped into a select for the specific row. – Janick Bernet Sep 06 '13 at 13:29
I think your second select DOES give the correct result. `SELECT * FROM table WHERE lang = 'FR' OR (lang = 'EN' AND ID NOT IN (SELECT ID FROM table WHERE lang = 'FR'))`. It gives one result per ID unless there are duplicate ID with the same language. See [Fiddle](http://sqlfiddle.com/#!2/9c167/6)./ – Rik Sep 06 '13 at 13:39
If have to specify the ID in each sql since I am getting the IDs from an externale json_decode php script so I will test your last proposal even if its no performance optimized – VolkaRacho Sep 06 '13 at 13:40
@Fiddle: Unless I made a major mistake, all queries should lead the same results, just likely differ in execution plans. – Janick Bernet Sep 06 '13 at 13:41
@VolkaRacho: I would recommend trying all the 3 first queries just wrapped inside another `SELECT` like I proposed and compare performance and then go from there. Ideally, your DB would be able to rewrite queries like this automatically and push down the ID = ?, but if you're using MySQL then probably you have to do it yourself. – Janick Bernet Sep 06 '13 at 13:44
@inflagranti Your probably right that all statements give the same result but the OP stated in his first line he uses MySql which does NOT KNOW `EXCEPT`. That's why i showed the second select in a [Fiddle-link](http://sqlfiddle.com/#!2/9c167/6) as an example. For the OP all those statements with `EXCEPT` won't work. – Rik Sep 06 '13 at 13:50
sorry if my feedback is taking quite long as mentioned i am not very experienced in SQL so modifing the not simplified SQL takes me ages due to the complexidy of the original query ;D I tested the [fiddle](http://sqlfiddle.com/#!2/9c167/6) but the problem here is that the ID is not specified and it give multiple results. I need a SQL were I specify ONE ID and get only one line of results. For each ID exist multiple LANG but each ID LANG combination is unique – VolkaRacho Sep 06 '13 at 14:10
I modified the fiddle [link](http://sqlfiddle.com/#!2/383ef/2) I think now it is doing what I need – VolkaRacho Sep 06 '13 at 14:22
thank you all for taking the time. have a good day and weekend! – VolkaRacho Sep 06 '13 at 14:25
1

Instead of looping the select you can also first build all the numbers needed and use `WHERE ID in (1,2,3)`. This would be faster. (added an answer for clarification) – Rik Sep 06 '13 at 14:31
I need the numbers in the order they are generated e.g. 3,1,2 that is why I am using union all – VolkaRacho Sep 06 '13 at 23:32
You're not guaranteed to get things back in order using union all though. See this answer to do it properly: http://stackoverflow.com/questions/610945/how-can-i-order-entries-in-a-union-without-order-by/610984#610984 – Janick Bernet Sep 07 '13 at 06:46

Marek · Answer 2 · 2013-09-06T14:09:27.330

0

select * from table where lang = 'FR' union
select t_en.* from table t_en left join table t_fr 
on (t_fr.id = t_en.id and t_fr.lang = 'FR' and t_en.lang = 'EN')
where t_fr.id is null and t_en.lang = 'EN'

edited Sep 06 '13 at 14:09

answered Sep 06 '13 at 13:36

Marek

7,337
1
22
33

i forgot where condition, first select is limited to fr. second select returns only en – Marek Sep 06 '13 at 13:41
Ok, now it makes sense. I'll fix my down vote as soon as I'm allowed to. – Janick Bernet Sep 06 '13 at 13:45
This still does not give the right answer. See a [Fiddle-example](http://sqlfiddle.com/#!2/9c167/13). You will get a 'DE' in your result. – Rik Sep 06 '13 at 13:56
@Rik yes, I added t_en.lang = 'EN' condition – Marek Sep 06 '13 at 14:10

score 0 · Answer 3 · answered Sep 06 '13 at 14:27

You can get your exact result in your question like this:

SELECT * FROM `table`
WHERE lang = 'FR'
OR (lang = 'EN' AND ID NOT IN (SELECT ID FROM `table` WHERE lang = 'FR'))

See Fiddle-example

If you really want to give the ID each time you can do

SELECT * FROM `table`
WHERE (ID=1) AND
( lang = 'FR'
  OR (lang = 'EN' AND ID NOT IN (SELECT ID FROM `table` WHERE lang = 'FR'))
)

This gives just one result per ID (Fiddle-example).

You can also use multiple ID's

SELECT * FROM `table`
WHERE (ID in (1,2,3)) AND
( lang = 'FR'
  OR (lang = 'EN' AND ID NOT IN (SELECT ID FROM `table` WHERE lang = 'FR'))
)

and build the (1,2,3) from your script and execute the select. See Fiddle-example.

score 0 · Answer 4 · answered Nov 12 '13 at 11:56

0

for better knowledge of case statement in mysql

http://dev.mysql.com/doc/refman/5.0/en/case.html

for better knowledge of if statement in mysql

http://dev.mysql.com/doc/refman/5.0/en/if.html

answered Nov 12 '13 at 11:56

Veerendra

2,562
2
22
39

score -1 · Answer 5 · edited Jul 15 '15 at 10:03

-1

SELECT * FROM TABLE WHERE ( LANG='FR' OR (lang='EN' AND ID NOT IN (SELECT ID FROM TABLE WHERE lang='FR' )))

edited Jul 15 '15 at 10:03

Bruce

8,609
8
54
83

answered Jul 15 '15 at 07:28

Billa Prasad

1

SQL IF ELSE / CASE clause in WHERE condition

5 Answers5

Linked