SUBQUERY not working

Question

SUBQUERY not working

Tim Morrison

SSC Eights!

Points: 894
More actions
September 1, 2004 at 8:53 am

#162610

Hi People!
Please excuse me, I'm very new to SQL and most of what I have learnt has come from here and Books On Line!!
I have an asset management DB - users are responsible for assets at designated sites - and I want to return searches for assets based on the sites that a user looks after.
I have a SELECT statement that has this clause on the end which is causing the problem:
WHERE tblAssets.SerialNumber LIKE '%' + @VALUE + '%'
AND tblSites.ID =ANY (SELECT * FROM tblUsers_Sites WHERE UserID = @userid)
I can't figure out what's wrong. I get this message;
"Only one expression can be specified in the select list when the subquery is not introduced with EXISTS."
I'd really appreciate any comments and suggestions; I know I've come to the right place, you guys & gals have really been my saviours in the past!
Best regards to all
Tim

Viewing 6 posts - 1 through 5 (of 5 total)

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

Dinesh Old Hand Points: 309 More actions · Answer 1

you are selecting all the rows of table "tblUsers_Sites" when you say "SELECT * FROM tblUsers_Sites WHERE UserID = @userid" and trying to equate it to tblSites.ID

Select only one column like "SELECT ID FROM tblUsers_Sites WHERE UserID = @userid"

MexiCano SSC Enthusiast Points: 179 More actions · Answer 2

Hi

probe this

WHERE tblAssets.SerialNumber LIKE '%' + @VALUE + '%'

AND tblSites.ID =(SELECT tblUsers_Sites.ID FROM tblUsers_Sites WHERE UserID = @userid)

Tim Morrison SSC Eights! Points: 894 More actions · Answer 3

Doh!!!!

Thanks Mexicano, I can't tell you how long I stared at that! How basic can you get? I was fixing on the EXISTS aspect of the error message when the answer was there in front of me all along.

Boy, do I feel silly. Thanks for taking the time to sort me out, it's really appreciated!

Tim

Tim Morrison SSC Eights! Points: 894 More actions · Answer 4

Sorry DINESH, it's before 6am as I type this and I'm not yet awake! The above message should have included you in my thanks. Lets hope my brain works a little better today although leaving you out of the previous postis not a good start.

Maybe I should go back to bed.....

e.francone SSChasing Mays Points: 650 More actions · Answer 5

WHERE tblAssets.SerialNumber LIKE '%' + @VALUE + '%'

AND tblSites.ID =(SELECT tblUsers_Sites.ID FROM tblUsers_Sites WHERE UserID = @userid)

If this expression ... =(SELECT tblUsers_Sites.ID FROM tblUsers_Sites WHERE UserID = @userid) return more than one row an error occur

Use ... IN (SELECT tblUsers_Sites.ID FROM tblUsers_Sites WHERE UserID = @userid)