Post reply

Where Account IN (......)

  • July 18, 2002 at 6:16 pm

    #78712

    Can anyone tell me why this wouldn't work:

    select distinct ACCOUNTNO From ContSupp Where accountno not in

    (

    select distinct accountno from contsupp where rectype = 'E'

    )

    There is data that falls into that category, but it always returns nothing???

    I'm stumped.

  • July 18, 2002 at 6:24 pm

    #433060

    I created a table called ContSupp with the column accountno and recttype and filled in some data and then executed your code as is and it worked. Something else must be happening. Can you give us some more details such as the table structure and sample data?

    Robert Marda

    Robert W. Marda
    Billing and OSS Specialist - SQL Programmer
    MCL Systems

  • July 18, 2002 at 6:28 pm

    #433063

    You don't have leading or trailing spaces by any chance do you??

    select distinct ACCOUNTNO From ContSupp Where accountno not in

    (

    select distinct accountno from contsupp where ltrim(rtrim(rectype)) = ''E''

    )

    You could try that.

    Cheers,

    Chris

  • July 18, 2002 at 6:28 pm

    #433062

    You don't have leading or trailing spaces by any chance do you??

    select distinct ACCOUNTNO From ContSupp Where accountno not in

    (

    select distinct accountno from contsupp where ltrim(rtrim(rectype)) = ''E''

    )

    You could try that.

    Cheers,

    Chris

  • July 18, 2002 at 6:29 pm

    #433065

    You don't have leading or trailing spaces by any chance do you??

    select distinct ACCOUNTNO From ContSupp Where accountno not in

    (

    select distinct accountno from contsupp where ltrim(rtrim(rectype)) = ''E''

    )

    You could try that.

    Cheers,

    Chris

  • July 18, 2002 at 6:29 pm

    #433064

    Also why not just do

    select distinct accountno from contsupp where rectype != 'E'

    which is the same thing.

    "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

  • July 18, 2002 at 6:31 pm

    #433066

    Oops, sorry, the computer kept prompting me to resend the information.

    Chris

  • July 18, 2002 at 6:34 pm

    #433067

    well I don't do that..because there are multiple records with the same accountno, that have different Rectype...

    So I want all the accountno's that don't have a rectype of 'E'...does that make sence? so:

    ContSupp:

    ACCOUNTNO RECTYP

    1 A

    1 E

    2 B

    3 E

    so after my query...all I want is the accountno '2'.. Make sence?

  • July 18, 2002 at 6:47 pm

    #433069

    Sorry brain gas. Try this

    SELECT DISTINCT accountno FROM ContSupp oQ WHERE NOT EXISTS (SELECT * FROM ContSupp iQ WHERE rectype = 'E' AND iQ.accountno = oQ.accountno)

    Your other code should work but this does the same thing, so see if it for some reason happens.

    "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

  • July 19, 2002 at 1:38 am

    #433078

    Solution is already provided, but I am wondering about the reason why this won't work.

    Could it have something to do with the 'problem' in the 'SQL POP Quiz' thread?

    The AccountNO in the inner select (in the WHERE clause) is not qualified. Maybe you should add table aliases to your query and see if that works.

    Anyway, the solution from Antares686 will do the trick.

  • July 19, 2002 at 5:54 am

    #433085

    You know I hadn't thought about that, but you might just be right. Without aliasing the tables and qualifying which one it is from it may be the same situation as the pop-quiz. I have seen the same question asked before and that concept did not come to mind. Try it and see what happens.

    "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply