MySQL：仅返回一个表中的行，其中另一个表的一列中的所有值都相同

I have two tables joined by a foreign key:

我有两个由外键连接的表：

main           secondary
----           --------------------        
 id             main_id(FK) | flag
----           --------------------  
  1                1          1
  2                1          1
  3                1          1
  4                2          0
                   2          1
                   3          0 
                   3          1
                   4          1 
                   4          1

I need to construct a query that will only return rows from 'main' if their corresponding rows in 'secondary' ALL have a flag = 1. In this example, the query should only return rows 1 and 4 from 'main'.

我需要构造一个查询，如果'secondary'ALL中的相应行有一个标志= 1，它只返回'main'中的行。在这个例子中，查询应该只返回'main'中的第1行和第4行。

main
----
 id
----
  1
  4

I've played around with COUNTs and NOT EXISTs, but I'm missing something fundamental here. The basic, stripped-down query I started with is:

我玩过COUNTs而不是EXISTs，但我在这里缺少一些基本的东西。我开始的基本的精简查询是：

SELECT main.id from main WHERE main.id IN (SELECT secondary.main_id from secondary WHERE flag = 1);

What other conditions do I need to set in order to get my desired result set?

我需要设置哪些其他条件才能获得所需的结果集？

3 个解决方案

#1

You could use a not in a subselect for flag <> 1

您可以在标记<> 1的子选择中使用not

SELECT main.id 
from main 
WHERE main.id NOT IN (SELECT secondary.main_id 
                             from secondary WHERE flag <> 1);

#2

You can try below query

您可以尝试以下查询

SELECT id 
FROM (
    SELECT id, COUNT(1) = SUM(CASE WHEN flag = 1 THEN 1 ELSE 0 END) is_valid
    FROM tableName
    GROUP by id)t1
WHERE is_valid = 1;

Hope this would help you out.

希望这会帮助你。

#3

Another approach which doesn't assume knowledge of the flag values (but does assume they are integers) would be to check the min and max values by main id

另一种不假设标志值知识（但假设它们是整数）的方法是通过主id检查最小值和最大值

MariaDB [sandbox]> select  id
    -> from main
    -> where id in
    -> (
    -> select  t.main_id from
    -> (
    -> select  s.main_id,min(s.flag) minflag, max(s.flag) maxflag
    -> from secondary s
    -> group by s.main_id
    -> ) t
    -> where t.minflag = t.maxflag
    -> ) ;
+------+
| id   |
+------+
|    1 |
|    4 |
+------+
2 rows in set (0.00 sec)

#1