Where Account IN (......)

Question

Where Account IN (......)

blinden

SSC Enthusiast

Points: 128
More actions
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.

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

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

Robert W Marda SSChampion Points: 13413 More actions · Answer 1

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

chris_rutherford Mr or Mrs. 500 Points: 590 More actions · Answer 2

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

chris_rutherford Mr or Mrs. 500 Points: 590 More actions · Answer 3

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

chris_rutherford Mr or Mrs. 500 Points: 590 More actions · Answer 4

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

Antares686 SSC Guru Points: 125444 More actions · Answer 5

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)

chris_rutherford Mr or Mrs. 500 Points: 590 More actions · Answer 6

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

Chris

blinden SSC Enthusiast Points: 128 More actions · Answer 7

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?

Antares686 SSC Guru Points: 125444 More actions · Answer 8

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)

NPeeters SSChampion Points: 12536 More actions · Answer 9

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.

Antares686 SSC Guru Points: 125444 More actions · Answer 10

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)