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

`AND application_id = 281656475 NOT IN` is not equivalent to the first query. It'll be the equivalent `AND true/false NOT IN` — Marc B, Sep 06 '13 at 16:35
I am not very experienced in SQL and to be honest I do not realy understand what "AND true/false NOT IN" means nor how I can coudl correct this. I also updated the first qury with ID = 4 which is also working in the updated fiddle — VolkaRacho, Sep 06 '13 at 16:41
nothing specific to sql, if it was any other language, it'd be more like `if (($x == 281656475) && some_other_function) { ... }`. — Marc B, Sep 06 '13 at 16:42

Gordon Linoff · Accepted Answer · 2013-09-06T21:21:25.937

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.

When running the query on the original table (which is quite big) it gives me 868,013 result. It displays results for all application_id It does not only output the one result with **application_id = 281656475** AND **language_code = 'EN'** I think my request might have not mentioned this clearly — VolkaRacho, Sep 06 '13 at 17:10
@VolkaRacho . . . If your condition is `application_id = 281656475 AND language_code = 'EN'`, why do you have the `OR` clause at all? Just use the first part of the `where` statement. — Gordon Linoff, Sep 06 '13 at 18:49
What I need to achieve is: IF `application_id = 281656475 AND language_code = 'PL'` exists then use `application_id = 281656475 AND language_code = 'PL'` . But in some cases `language_code = 'PL'` does not exists for the ID (in this case: `application_id = 281656475` ). If this is the case the query should output the values for this `application_id = 281656475` but from the row where `language_code = 'EN'` which always exist. I have only very limited knowledge of SQL that is why I asked in the linked stackoverflow question for a solution. And here someone else proposed the solution with the OR — VolkaRacho, Sep 06 '13 at 21:15
AWESOME! The first script of your EDIT does excactly what I need since it is also working with a union all and displays one result for each application_id. Thanks a lot! — VolkaRacho, Sep 08 '13 at 10:59

score 2 · Answer 2 · answered Sep 06 '13 at 16:40

you can replace

 not IN

with

 and NOT exists


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

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

2 Answers2