Problem with nested query

Question

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 the 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!

Do some formatting on your query please.

Vladislav Rastrusny
– Vladislav Rastrusny

2011-04-21 20:19:21 +00:00
Commented Apr 21, 2011 at 20:19 — Vladislav Rastrusny
– Vladislav Rastrusny, Commented Apr 21, 2011 at 20:19
Can you send through some DUMMY Data dump at all?

Robbie Tapping
– Robbie Tapping

2011-04-21 20:28:34 +00:00
Commented Apr 21, 2011 at 20:28 — Robbie Tapping
– Robbie Tapping, Commented Apr 21, 2011 at 20:28

Robbie Tapping · Accepted Answer · 2011-04-21 20:42:17Z

1

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

answered Apr 21, 2011 at 20:42

Robbie Tapping

2,5561 gold badge17 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

WixLove Over a year ago

This completely worked and I'm floored. Why would changing the dates to include BETWEEN make the difference? Thanks sooo much!

Conrad Frix Over a year ago

@WixLove. Did you change the BETWEEN and change the alias in the WHERE clause on the sub query or just do the between? I'm curious because otherwise this makes no sense to me.

Robbie Tapping Over a year ago

Hey, the problem you had was that your OR clauses in your WHERE Clauses where not wrapped correctly with Brackets, so you had items coming through with the OR Clause instead of contraining the referraldate and the createdate. Hope this helps.

Conrad Frix Over a year ago

Oh I see now its the parens nice catch +1

Robbie Tapping Over a year ago

@WixLove, do you understand why you need to put the Brackets around the Checking of the CreatedDate OR the Referral date??

|

Kyle Sletten · Accepted Answer · 2011-04-21 20:38:41Z

0

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;

edited Apr 21, 2011 at 20:38

answered Apr 21, 2011 at 20:27

Kyle Sletten

5,4333 gold badges28 silver badges39 bronze badges

1 Comment

WixLove Over a year ago

I think this might be useful and I'll file this away for this future. Thank you!

Conrad Frix · Accepted Answer · 2011-04-21 21:28:43Z

0

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 (....

edited Apr 21, 2011 at 21:28

answered Apr 21, 2011 at 20:47

Conrad Frix

52.8k13 gold badges99 silver badges158 bronze badges

2 Comments

WixLove Over a year ago

Oh wow! Thanks! Did that fix but I still get IDs that shouldn't be there.

Conrad Frix Over a year ago

@WixLove You'll need to provide some Dummy Data as Robbie Tapping suggested. You can do that by pressing the edit link under you question and adding it there.

Collectives™ on Stack Overflow

Problem with nested query

3 Answers 3

9 Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

9 Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related