mysql返回表的一列_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

3

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

0

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

0

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)