我有一对多的关系,看起来像这样:
|父| |儿童| | 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子关系查询帮助
发布评论