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
)
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
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