Problem with nested query

I am trying to retrieve in my result list a list of IDs where two conditions are satisfied: 1) the ID has a service (determined by referral or note) within a specified time 7/1/2009-6/30/2010 2) the ID has not appeared before the specified time

I've come up with this long query but it still brings back those that have a previous service:

SELECT DISTINCT c.id, 
                c.lastname, 
                c.firstname 
FROM   roundtable rt 
       INNER JOIN clients c 
         ON c.id = rt.clientid 
       LEFT OUTER JOIN notes n 
         ON c.id = n.clientid 
       LEFT OUTER JOIN referral rf 
         ON c.id = rf.clientid 
WHERE  ( rf.referraldate>='2009-07-01' 
         AND rf.referraldate<='2010-06-30' ) 
        OR ( n.createddate>='2009-07-01' 
             AND n.createddate<='2010-06-30' ) 
           AND c.id NOT IN (SELECT DISTINCT clt.id 
                            FROM   roundtable rtb 
                                   INNER JOIN clients clt 
                                     ON clt.id = rtb.clientid 
                                   LEFT OUTER JOIN notes nts 
                                     ON clt.id = nts.clientid 
                                   LEFT OUTER JOIN referral ref 
                                     ON clt.id = ref.clientid 
                            WHERE  ( rf.referraldate < '2009-07-01' ) 
                                    OR ( n.createddate < '2009-07-01' )) 
ORDER  BY c.lastname, 
          c.firstname 

For example: ID, ReferralDate, NoteCreatedDate

4, 2/12/2008, 3/12/2008

4, 7/15/2009, 7/30/2009

6, 5/30/2008, 2/26/2007

8, 7/20/2009, 3/20/2008

9, 7/20/2009, 10/3/2009

So IDs 4, 6 and 8 should not be in the return list since ID 4 has a previous referral and note outside the time period, and ID 6 has both referral and notes outside the time period while ID 8 has one note outside t开发者_StackOverflow社区he time period. In this case, ID 9 should be the only one returned since it has dates in the time period and no previous records.

Thanks!

SELECT DISTINCT 
          c.id
        , c.lastname
        , c.firstname
FROM 
    roundtable rt 
    INNER JOIN 
            clients c
                    on c.id = rt.clientid 
    LEFT OUTER JOIN 
            notes n 
                    on c.id = n.clientid 
    LEFT OUTER JOIN  
            referral rf 
                    on c.id = rf.clientid 
WHERE 
        (rf.referraldate BETWEEN '2009-07-01' AND '2010-06-30' or n.createddate BETWEEN '2009-07-01' and '2010-06-30')
AND 
        c.id not in (SELECT DISTINCT 
                                      clt.id 
                     FROM 
                        roundtable rtb
                        INNER JOIN
                                    clients clt 
                                            on clt.id = rtb.clientid
                        LEFT OUTER JOIN 
                                    notes nts 
                                            on clt.id = nts.clientid
                        LEFT OUTER JOIN  
                                    referral ref 
                                            on clt.id = ref.clientid
                    WHERE 
                            (rf.referraldate < '2009-07-01' or  n.createddate <'2009-07-01'))
ORDER BY 
        c.lastname, c.firstname

You don't really need to be joining, since you're not pulling the data back. You can simply check if there even exists a record in either of those tables matching the criteria. The EXISTS keyword is really nice too because it stops looking as soon as it finds a match.

SET @begin_date = '2009-07-01';
SET @end_date = '2010-06-30';

SELECT
    c.id,
    c.lastname,
    c.firstname
FROM
    clients c
WHERE
    EXISTS (
        SELECT
            *
        FROM
            referrer r
        WHERE
            r.clientid = c.id
        AND
            r.referraldate >= @begin_date
        AND
            r.referraldate <= @end_date)
OR
    EXISTS (
        SELECT
            *
        FROM
            notes n
        WHERE
            n.clientid = c.id
        AND
            n.createdate >= @begin_date
        AND
            n.createdate <= @end_date)
ORDER BY
    c.lastname,
    c.firstname;

It looks like a copy paste error. You used different table aliases in the sub query except for the WHERE

You wrote

      WHERE  ( rf.referraldate < '2009-07-01' ) 
                       OR ( n.createddate < '2009-07-01' ) 

When I think you meant

     WHERE  ( ref.referraldate < '2009-07-01' ) 
                       OR ( nts.createddate < '2009-07-01' ) 

Additionally as Robbie picked up on you need and extra set of parens. Using between as he did makes it easier since you don't need to nest the parens

e.g.

WHERE
(
    (rf.referraldate>='2009-07-01' 
       AND rf.referraldate<='2010-06-30' ) 
     OR ( n.createddate>='2009-07-01' 
     AND n.createddate<='2010-06-30' ) 
)
AND NOT IN (....