Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2008
    Posts
    55

    Unanswered: Select where exists

    Hello,

    I am having problems using select where exists.

    here is what the data look like:
    acctnbr fund
    000001 1212
    000001 1212
    000001 1212
    000002 3434
    000002 3434
    000002 5656

    i am trying to select any occurence of an acctnbr which has a fund that is different from the other funds for that acctnbr. In the above data it would be the acctnbr 000002 and fund 5656.
    My query seems to get in a loop. what's wrong with it? any suggestions?

    Thank you!

    select a.acctnbr, a.fund
    from tb945 a
    where exists (select 1 from tb945 x
    where a.acctnbr = x.acctnbr
    and a.fund <> x.fund)
    Last edited by KevinYC; 11-13-08 at 17:16.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    define "last" occurrence -- there is no sequence column here
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2008
    Posts
    55
    Quote Originally Posted by r937
    define "last" occurrence -- there is no sequence column here
    thanks for your response, r937.
    I have re-phrased my question. i am looking for any occurence of acctnbr whose fund is different from the other funds for that acctnbr. In the data above, it would be that very last row. does this make sense?

    Thanks again.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT acctnbr 
      FROM tb945 
    GROUP
        BY acctnbr
    HAVING COUNT(DISTINCT fund) > 1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Aug 2008
    Posts
    55
    Quote Originally Posted by r937
    Code:
    SELECT acctnbr 
      FROM tb945 
    GROUP
        BY acctnbr
    HAVING COUNT(DISTINCT fund) > 1

    Thank you, R937! I just need to ask one more thing..
    How can I select the acctnbr and the different funds for the result table? In this case, I would like to see 000002, 3434, 5656 printed in the result table. How can I do that?

    Thanks again!
    Last edited by KevinYC; 11-18-08 at 14:32.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    do it the same way you tried it in post #1 -- with a subquery
    Code:
    select a.acctnbr, a.fund 
    from tb945 a
    where a.acctnbr IN 
         ( SELECT acctnbr 
             FROM tb945 
           GROUP
               BY acctnbr
           HAVING COUNT(DISTINCT fund) > 1 )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Aug 2008
    Posts
    55
    Thank you so much!

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I might suggest:
    Code:
    SELECT DISTINCT a.acctnbr, a.fund, b.fund
       FROM tb945 AS a
       JOIN tb945 AS b
          ON (b.acctnbr = a.acctnbr
          AND a.fund < b.fund)
    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •