SQL子关系查询帮助

本文介绍了SQL子关系查询帮助的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

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

|父| |儿童| | 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.