SQL查询以获取具有子记录列表的父表记录 [英] SQL Query to get records of parent table that have a list of child records

问题描述

我在MS SQL Server 2005数据库中有两个表，父表和子表，其中父表可能与许多子记录相关. [Child.parent_id]与[parent.id]相关.子表也有[foo]列，我需要带回父表中的所有记录，其中[child.foo]在一个到多个参数中的每个参数上都匹配.例如，我希望所有[child.foo]值为'fizz'和[child.foo]值为'buzz'的父记录. 我已经尝试了以下查询，但是它返回的记录仅与一个匹配.

I have two tables in an MS SQL Server 2005 database, parent and child, where the parent may be related to many child records. [Child.parent_id] is related to [parent.id]. The child table also has column [foo] I need to bring back all records in the parent table where [child.foo] matches on each of one to many parameters. For example I would like all parent records that have a [child.foo] value of 'fizz' and a [child.foo] value of 'buzz.' I have tried the below query, but it is returning records that match on only one.

SELECT     Parent.ID
FROM         Parent 
INNER JOIN Child ON Parent.ID = Child.parent_id
WHERE     (Child.foo = 'fizz')
UNION ALL
SELECT     Parent_1.ID
FROM         Parent AS Parent_1 
INNER JOIN Child AS Child_1 ON Parent_1.ID = Child_1.parent_id
WHERE     (Child_1.foo = 'buzz')

推荐答案

这将返回所有父记录，这些记录具有[至少]一个带有"fizz" foo的孩子和[至少]一个带有"buzz" foo的孩子.我认为这个问题是必需的.

This will return all Parent records which have [at least] one child with a 'fizz' foo AND [at least] one child with 'buzz' foo. Which is what I think is required in the question.

该查询虽然可能不是最佳选择，但从某种意义上讲它是通用的，它可以与大多数SQL实现一起使用，不仅可以支持CTE，子查询和相关结构的支持的更现代的SQL实现.

Also, while being potentially sub-optimal, this query is generic in a sense that it will work with most SQL implementation, not only the more modern ones, where the support of CTE, subqueries and related constructs are supported.

   SELECT DISTINCT Parent.ID
    FROM Parent
    JOIN Child C1 ON Parent.ID = C1.parent_Id
    JOIN Child C2 ON Parent.ID = C2.parent_id
    WHERE C1.foo = 'fizz'
      AND C2.foo = 'buzz'

修改:
既然Joel Potter已在他的答案中修复了该查询，我们可能同意他的方法比上面列出的查询具有多个优点(请给他一些+ reps).特别是:

Edit:
Now that Joel Potter has fixed the query in his answer, we probably agree that his approach has several advantages over the query listed above (please give him a few +reps). In particular:

当我们添加或删除列foo的目标值时，查询的
• 结构不会改变.
• 通过服务器本身更容易优化查询
• 查询的结构允许它处理过滤器定义的变化.例如，我们可以查询所有有子代的父代，例如存在5个可能的foo值中的2个.

• the structure of the query doesn't change when we add, or remove, targeted values for the column foo.
• the query is probably more easily optimized [by the server itself]
• the structure of the query allows it to handle variations on the definition of the filter. For example we can query all Parents which have children such that say 2 of 5 possible values of foo are present.

下面是Joel的查询，对查询进行了一些修改，以显示如何将其扩展为2个以上的foo值.

Following is Joel's query here, slightly modified, to show how it can be expanded for more than 2 foo values.

SELECT Parent.Id
FROM Parent
INNER JOIN Child on Parent.Id = child.parent_id
WHERE Child.foo IN ('fizz', 'buzz')  -- or say, ... IN ('fizz', 'buzz', 'bang', 'dong')
GROUP BY Parent.Id
HAVING COUNT(DISTINCT Child.foo) = 2  -- or 4 ...  

这篇关于SQL查询以获取具有子记录列表的父表记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！