April 20, 2015 at 12:04 am
Hi all,
I am getting error when I passed multiplt rows in less than condition:
create table #t1
( ID int)
INSERT INTO #t1
SELECT 1 UNION ALL SELECT 5 UNION ALL SELECT 8
CREATE TABLE #t2
(ID int)
INSERT INTO #t2
SELECT 3 UNION ALL SELECT 20 UNION ALL SELECT 4
SELECT ID FROM #t2
WHERE ID < (SELECT ID FROM #t1)
Error is: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
How to pass multiple values in this condition?
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 20, 2015 at 12:16 am
kapil_kk (4/20/2015)
Hi all,I am getting error when I passed multiplt rows in less than condition:
create table #t1
( ID int)
INSERT INTO #t1
SELECT 1 UNION ALL SELECT 5 UNION ALL SELECT 8
CREATE TABLE #t2
(ID int)
INSERT INTO #t2
SELECT 3 UNION ALL SELECT 20 UNION ALL SELECT 4
SELECT ID FROM #t2
WHERE ID < (SELECT ID FROM #t1)
Error is: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
How to pass multiple values in this condition?
Passing multiple values to a 'less than' condition does not make logical sense.
Is this what you are trying to achieve? If not, what results are you trying to return?
select ID
from #t2
where ID < (select max(ID)
from #t1
)
April 20, 2015 at 2:07 am
It depends what you need to do. If you're checking that the ID from #t2 is lesst than all the IDs in #t1, you can use the ALL keyword.
SELECT ID FROM #t2
WHERE ID < ALL (SELECT ID FROM #t1)
What's the expected results based on the sample data you posted?
-- Gianluca Sartori
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy