Subquery Problem?? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Subquery Problem??

Author

Topic

irene casper
Starting Member

4 Posts

Posted - 2006-12-04 : 00:44:01

query1:

SELECT js_serialTo FROM ltjSerial WHERE js_serialFrom = 'aaa' order by js_serialTo

result return: '001','002','003','004'

query2:

SELECT s_serial FROM ltSerialMaster WHERE s_serial IN ('000','001','002','003','004')

query3:

SELECT s_serial FROM ltSerialMaster WHERE s_serial IN ('000', (SELECT js_serialTo FROM ltjSerial WHERE js_serialFrom = 'aaa' order by js_serialTo))

query2 can be executed, but query3 shows "subquery return more than 1 rows"; can't the subquery returns more than 1 row in this context?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-04 : 00:54:32

How about this:

SELECT s_serial 
FROM ltSerialMaster 
WHERE s_serial IN 
(
	SELECT '000' as js_serialTo
	UNION ALL
	SELECT js_serialTo FROM ltjSerial WHERE js_serialFrom = 'aaa' order by js_serialTo
)

Harsh Athalye
India.
"Nothing is Impossible"

irene casper
Starting Member

4 Posts

Posted - 2006-12-04 : 01:22:00

harsh_athalye, i tried your method, it shows: "illegal mix of collations..."

i attempt these, it works;)


SELECT s_serial FROM ltSerialMaster WHERE (s_serial = '000' OR s_serial IN (SELECT js_serialTo FROM ltjSerial WHERE js_serialFrom = 'aaa' order by js_serialTo))

harsh_athalye, thanks anyway:)

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-12-04 : 01:37:33

bec there are more than 1 row (run the subquery after IN)

in this context, whatever is selected from this subquery is expected to be 1 value only

--------------------
keeping it simple...

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-04 : 01:43:14

I think it's because value '000' is assigned database default collation whereas js_serialTo has different collation than database.

It's a guess, I may be wrong!

quote:
bec there are more than 1 row (run the subquery after IN)

~~No, there are four rows returned for that query.~~

Harsh Athalye
India.
"Nothing is Impossible"

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-12-04 : 01:50:08

huh? which is more than 1 row noh?

quote:
Originally posted by harsh_athalye

quote:
bec there are more than 1 row (run the subquery after IN)

No, there are four rows returned for that query.

Harsh Athalye
India.
"Nothing is Impossible"

--------------------
keeping it simple...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources