SQL don't like the subquery  
Author Message
ChrisBaum





PostPosted: Tue Aug 31 14:20:40 CDT 2004 Top

SQL Server Developer >> SQL don't like the subquery









@iKundTjanstID END)

AND (tblTjanster.blKasserad = 0)

AND (tblTjanster.iUppehallID = 0)





to meny results in the sub query says the error

Any advise to get it work



//Kenneth

SQL Server21  
 
 
David





PostPosted: Tue Aug 31 14:20:40 CDT 2004 Top

SQL Server Developer >> SQL don't like the subquery A fragment of code like this doesn't give us much to go on. At a guess, I'd
say that sUserID isn't unique in the table tblUsersMemberKundTjanster, which
would explain the error message "Subquery returned more than 1 value". If
you need more help: http://www.aspfaq.com/5006

--
David Portas
SQL Server MVP
--


 
 
Kenneth





PostPosted: Tue Aug 31 14:36:00 CDT 2004 Top

SQL Server Developer >> SQL don't like the subquery It's right it's not unique, the result is more the one rows in the subquery





Any advice?


//Kenneth







>A fragment of code like this doesn't give us much to go on. At a guess, I'd
> say that sUserID isn't unique in the table tblUsersMemberKundTjanster,
> which
> would explain the error message "Subquery returned more than 1 value". If
> you need more help: http://www.aspfaq.com/5006
>
> --
> David Portas
> SQL Server MVP
> --
>
>


 
 
Kenneth





PostPosted: Tue Aug 31 15:00:39 CDT 2004 Top

SQL Server Developer >> SQL don't like the subquery I will try it in the morrning

//Kenneth





> Correction,
>



> AND
> (

> and exists (SELECT * FROM tblUsersMemberKundTjanster WHERE
> tblUsersMemberKundTjanster.iKundTjanstID = tblTjanster.iKundTjanstID

> or

> )
> AND (tblTjanster.blKasserad = 0)
> AND (tblTjanster.iUppehallID = 0)
>
>
>
> AMB
>

>
>> Use EXISTS instead IN.
>>



>> AND
>> (

>> and exists (SELECT * FROM tblUsersMemberKundTjanster WHERE sUserID =

>> or

>> )
>> AND (tblTjanster.blKasserad = 0)
>> AND (tblTjanster.iUppehallID = 0)
>>
>>
>>
>> AMB
>>

>>

>> >

>> >

>> >

>> > (SELECT
>> > iKundTjanstID FROM tblUsersMemberKundTjanster WHERE sUserID =


>> >
>> > AND (tblTjanster.blKasserad = 0)
>> >
>> > AND (tblTjanster.iUppehallID = 0)
>> >
>> >
>> >
>> >
>> >
>> > to meny results in the sub query says the error
>> >
>> > Any advise to get it work
>> >
>> >
>> >
>> > //Kenneth
>> >
>> >
>> >


 
 
David





PostPosted: Tue Aug 31 15:11:10 CDT 2004 Top

SQL Server Developer >> SQL don't like the subquery

You can't rely on that. It depends on the execution order of the predicates,
which you can't control. Try this:

...



AND (tblTjanster.iKundTjanstID IN
(SELECT iKundTjanstID
FROM tblUsersMemberKundTjanster



AND tblTjanster.blKasserad = 0
AND tblTjanster.iUppehallID = 0

--
David Portas
SQL Server MVP
--


 
 
Kenneth





PostPosted: Wed Sep 01 12:30:52 CDT 2004 Top

SQL Server Developer >> SQL don't like the subquery Thanks works as I want

//Kenneth




>
> You can't rely on that. It depends on the execution order of the
> predicates,
> which you can't control. Try this:
>
> ...



> AND (tblTjanster.iKundTjanstID IN
> (SELECT iKundTjanstID
> FROM tblUsersMemberKundTjanster



> AND tblTjanster.blKasserad = 0
> AND tblTjanster.iUppehallID = 0
>
> --
> David Portas
> SQL Server MVP
> --
>
>