SQL子关系查询帮助 [英] SQL Child relation query help

问题描述

我有一对多的关系，看起来像这样：

  |父| |儿童| 
 | id | | id | 
 | | | parentID | 
 | | |日期| 
  

我试图构造一个查询，使我得到所有的父母有孩子记录ALL都有一个指定日期之前的日期。

类似这样

  SELECT * FROM parent 
 JOIN child on child.parentid = parent.id 
 WHERE child.date< = '10 / 13/2010'
  pre> 
 
 
但是这样做的问题是我让父母有孩子在指定的日期之前的日期，并有孩子记录日期指定的日期后，当我想
 
 
 
有没有人对如何处理这种情况有一些建议？
 
 
 
谢谢！
解决方案
使用：
  SELECT p。* 
 FROM PARENT p 
 WHERE EXISTS（SELECT NULL 
 FROM CHILD c 
 WHERE c.parentid = p.id 
 AND c.date< ='2010-10-13'）
 AND NOT EXISTS（SELECT NULL 
 FROM CHILD c 
 WHERE c.parentid = p.id 
 AND c.date> '2010-10-13'）
  
每个人都会告诉你使用JOIN ，但通常他们不知道使用的影响 - 如果你不需要支持表中的信息，你不应该加入它。这是因为在这种情况下多个孩子会产生重复的PARENT记录。 JOIN和DISTINCT或GROUP BY vs IN或EXISTS之间的折衷可能是平等的，但没有正确处理重复数据的麻烦。
 
I have a one to many relation that looks like this:
| Parent |  | Child  |
|   id   |  |   id   |
|        |  |parentID|
|        |  |  date  |
And I am trying to structure a query such that I get all of the parents who have children records which ALL have a date before a specified date.

Something like this 
SELECT * FROM parent
JOIN child on child.parentid = parent.id
WHERE child.date <= '10/13/2010'
But the problem with this is I get parents that have children with a date before the date specified and have child records with a date after the date specified, when I want ONLY the parents of children with a date before the given date.

Does anyone have some suggestions on how to handle this case?

Thanks!
解决方案
Use:
SELECT p.*
  FROM PARENT p
 WHERE EXISTS(SELECT NULL
                FROM CHILD c
               WHERE c.parentid = p.id
                 AND c.date <= '2010-10-13')
   AND NOT EXISTS(SELECT NULL
                    FROM CHILD c
                   WHERE c.parentid = p.id
                     AND c.date > '2010-10-13')
Everyone will tell you to use JOINs "because they're faster", but typically they aren't aware of the impact of using them -- if you don't need the information from a supporting table, you shouldn't be joining to it.  That's because more than one child in this situation would produce duplicate PARENT records.  The trade-off between a JOIN and DISTINCT or GROUP BY vs IN or EXISTS is probably par, but without the hassle of dealing with the duplicated data properly.

                        
这篇关于SQL子关系查询帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！