MySQL - 在 WHERE 子句中的 NOT IN 查询具有相同的结构在第一个表上工作但不在第二个表上

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

提问by VolkaRacho

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.

我想将一个简单的 MySQL 查询从一个表调整到另一个表。第一个查询按预期工作，但第二个修改后的查询未显示预期结果，但我无法弄清楚结构中的任何差异。

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

这个问题与我之前认为已解决的问题有关：

SQL IF ELSE / CASE clause in WHERE condition

WHERE 条件中的 SQL IF ELSE / CASE 子句

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.

我想在 SQL SELECT 中编写一个 WHERE 条件，该条件应该为每个 ID 最多显示一个结果。但仅当 LANG 为 FR 或 EN 时才应显示结果。在顶部 FR 应该是首选，并且 EN 应该仅在没有 FR 可用于 ID 的情况下显示为替代。所以结果看起来像这样。

Here the working query. Here is the SQL Fiddle LINK

这里是工作查询。这是 SQL 小提琴链接

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

这是结构相同的查询。这是 SQL 小提琴链接

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)

编辑：最适合我的解决方案（因为我需要将它与 union all 结合起来，并且每个 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
                              )
       )
      )

回答by Gordon Linoff

Your query fails because you are doing a comparison andnot in:

您的查询失败，因为您正在进行比较并且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:

MySQL 将进行第一次比较，并将布尔结果转换为整数not in。根据第一个查询的结构，您需要：

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 NULLcan cause NOT INto fail.

我还添加了application_id is not null，因为NULL会导致NOT IN失败。

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.

这使用 MySQL 特定的语法。您的问题未标记为 MySQL，但确实使用了 MySQL 语法。

回答by danisius

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