使用另一个表中的行过滤查询的输出 [英] Filter the output of a query with row from another table

问题描述

我需要一些代码方面的帮助

I need some help with some code

我有2个表，分别是"stuff"和"something"，并且我有此信息:

I have 2 tables called "stuff" and "something" and I have this info:

               stuff                                  something
+-------------+---------------------+    +-------------+---------------------+
| member_id   |     group_id        |    |  group_id   |      some_id        |
+-------------+---------------------+    +-------------+---------------------+
|      11     |         aa          |    |      aa     |         7           |
|      11     |         bb          |    |      dd     |         8           |
|      22     |         bb          |    |      bb     |         9           |
|      11     |         cc          |    +-------------+---------------------+
|      22     |         cc          |
|      33     |         cc          |
|      11     |         dd          |
+-------------+---------------------+

此查询:

SELECT  group_id
FROM    stuff a
WHERE   member_id IN (11)  -- <<== list of member_id (can be 11,22 or 22,33 or 11,22,33)
    AND
    EXISTS
    (
      SELECT group_id
      FROM stuff b
      WHERE a.group_id = b.group_id
      GROUP BY group_id
      HAVING COUNT(*) = 1                   -- <<== number of member_id
    )
GROUP   BY group_id
HAVING  COUNT(*) = 1                            -- <<== number of member_id

将返回aa和dd.

我的问题是:如果some_id=7，我该如何过滤结果?

My question is: how do I filter the result if some_id=7 ?

推荐答案

SELECT  `group_id`
FROM    `stuff` a
WHERE   `member_id` IN (11) -- <<== list of member_id (can be 11,22 or 22,33 or 11,22,33)
AND
EXISTS
(
    SELECT  `group_id`
    FROM    `stuff` b
    WHERE a.`group_id` = b.`group_id`
    AND
    EXISTS
    (
        SELECT  c.`group_id`
        FROM      `something` c
        WHERE   c.`group_id` = b.`group_id`
        AND     c.`some_id`=7                        -- <<== number of some_id=7
    )  
    GROUP BY `group_id`
    HAVING COUNT(*) = 1               -- <<== number of member_id
)
GROUP   BY `group_id`
HAVING  COUNT(*) =1               -- <<== number of member_id

这将返回aa ...我想要什么！

This will return aa ...what I wanted!

这篇关于使用另一个表中的行过滤查询的输出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！