MySQL - NOT IN query in WHERE clause with same structure working on first table but not on second table

Question

I want to adapt a simple MySQL query from one table to another table. The first query is working as intended but the second modified query is not displaying the expected result, but I can not figure out any difference in the structure.

This question is related to my previous question which I thought was solved:

SQL IF ELSE / CASE clause in WHERE condition

Shortly explained qhat the query should do:

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.

Here the working query. Here is the SQL Fiddle LINK

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

Here is the structual identical query. Here is the SQL Fiddle LINK

SELECT * FROM `epf_application_detail`
WHERE application_id = 281656475
AND language_code = 'PL'
OR (language_code = 'EN' AND application_id = 281656475 NOT IN (SELECT application_id FROM `epf_application_detail` WHERE language_code = 'PL'))

If the second fiddle would work properly it should display

+--------------+----------------+---------------+-----------+
|  export_date | application_id | language_code |    title  |
+--------------+----------------+---------------+-----------+
|1377594004198 | 281656475      | 'EN'          | 'PAC-MAN' |
+--------------+----------------+---------------+-----------+

I have absolutly no idea what coudl be the difference, so any help is much appretiated

EDIT: SOLUTION that worked best for me (since I needed to combine it with union all and needed one result for each application_id)

SELECT *
FROM `epf_application_detail`
WHERE application_id = 281656475 AND
      (language_code = 'PL' OR
       (language_code = 'EN' AND
        application_id NOT IN (SELECT application_id
                               FROM `epf_application_detail`
                               WHERE language_code = 'PL' and application_id is not null
                              )
       )
      )

Answer 1

Your query fails because you are doing a comparison and not in:

SELECT *
FROM `epf_application_detail`
WHERE (application_id = 281656475 AND language_code = 'PL') OR
      (language_code = 'EN' AND
       application_id = 281656475 NOT IN (SELECT application_id
----------------------------------^
                                          FROM `epf_application_detail` WHERE language_code = 'PL'))

MySQL is going to do the first comparison, and convert the boolean result to an integer for the not in. Based on the structure of the first query, you want:

SELECT *
FROM `epf_application_detail`
WHERE (application_id = 281656475 AND language_code = 'PL') OR
      (language_code = 'EN' AND
       application_id NOT IN (SELECT application_id
                              FROM `epf_application_detail`
                              WHERE language_code = 'PL' and application_id is not null
                             )
      )

I also added application_id is not null, because NULL can cause NOT IN to fail.

EDIT:

Based on your comment, this should capture your logic:

SELECT *
FROM `epf_application_detail`
WHERE application_id = 281656475 AND
      (language_code = 'PL' OR
       (language_code = 'EN' AND
        application_id NOT IN (SELECT application_id
                               FROM `epf_application_detail`
                               WHERE language_code = 'PL' and application_id is not null
                              )
       )
      )

If you are only looking for one row from the detail table, the following is much simpler:

SELECT *
FROM `epf_application_detail`
WHERE application_id = 281656475 AND
      language_code in ('PL', 'EN')
ORDER BY language_code = 'PL' desc
LIMIT 1;

This uses MySQL-specific syntax. Your question is not tagged MySQL but does use MySQL syntax.