SQL SQL relationship query help

Question

I have a one to large relationship that looks like this:

| Parent |  | Child  |
|   id   |  |   id   |
|        |  |parentID|
|        |  |  date  |

And I'm trying to structure the request in such a way that I get all parents who have records of children who ALL have a date before the specified date.

Something like that

SELECT * FROM parent
JOIN child on child.parentid = parent.id
WHERE child.date <= '10/13/2010'

But the problem is that I get parents who have children with a date before the specified date and have child records with the date after the specified date, when I want ONLY the parents of children with the date before the specified date.

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

Thank!

Answer 1

Using:

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')

JOINs " ", - , . , . JOIN DISTINCT GROUP BY vs IN EXISTS, -, , .

Answer 2

SELECT
  *
FROM
  Parent
WHERE
  EXISTS (SELECT * FROM Child WHERE Child.ParentId = Parent.Id AND [date] <= '2010-10-13')
  AND
  NOT EXISTS (SELECT * FROM Child WHERE Child.ParentId = Parent.Id AND [date] > '2010-10-13')

Answer 3

:

• , X
• , , , Date X

. . HAVING, , X.

SELECT P.*
FROM Parent P
WHERE P.id IN
(
    SELECT C.parentID
    FROM Child C
    GROUP BY C.parentID
    HAVING MAX(CASE WHEN date > '2010-10-13' THEN 1 ELSE 0 END) = 0
    /* do not return children that have a date after 2010-10-13 */
)   

, . (SQL Server)

("date" is called "mydate" to avoid having to reserve a word.)

CREATE TABLE Parent (id INT PRIMARY KEY);
CREATE TABLE Child (id INT IDENTITY PRIMARY KEY, parentID INT NOT NULL REFERENCES Parent(id), mydate DATE );

INSERT INTO Parent VALUES (1);
INSERT INTO Parent VALUES (2);
INSERT INTO Parent VALUES (3);
INSERT INTO Parent VALUES (4);

INSERT INTO Child (parentID, mydate) VALUES (1,'2010-10-11')
INSERT INTO Child (parentID, mydate) VALUES (1,'2010-10-12')
INSERT INTO Child (parentID, mydate) VALUES (1,'2010-10-13')

INSERT INTO Child (parentID, mydate) VALUES (2,'2010-10-12')
INSERT INTO Child (parentID, mydate) VALUES (2,'2010-10-13')
INSERT INTO Child (parentID, mydate) VALUES (2,'2010-10-14')

INSERT INTO Child (parentID, mydate) VALUES (3,'2010-10-14')
INSERT INTO Child (parentID, mydate) VALUES (3,'2010-10-15')
INSERT INTO Child (parentID, mydate) VALUES (3,'2010-10-16')