T-SQL - Select Using SubQuery

Question

I have a SQL query where I have in my where clause a list into a IN condition.

Example of what I mean:

WHERE Id IN (11111,11112)

And what I try to do is the following: I have a linked server query where I have an IN clause.

And I have a subquery where I use a JOIN table to cross data but I need that in the IN condition use the same Id like the main query.

I hope you guys understand what I try to do:

Here's my code:

SELECT 
Name, 
Street, 
Number,
(SELECT loginUser FROM [LinkedServer].[Database].[dbo].[Users] T1 
INNER JOIN [LinkedServer].[Database].[dbo].[General] T2
ON T2.IdUser = T1.Id
WHERE T2.Id IN (11111,11112,11113,11114,11115)
)
FROM [LinkedServer].[Database].[dbo].[General]
WHERE Id IN (11111,11112,11113,11114,11115)

i get this error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Yes - and what's the question? You seem to have your query all worked out .... — marc_s
– marc_s, Commented Jul 10, 2014 at 10:57
Can you provide your actual query. You seem to be referencing the exact same table 3 times, which is obfuscating your real problem. — gvee
– gvee, Commented Jul 10, 2014 at 11:03

Matas Vaitkevicius · Accepted Answer · 2014-07-10 11:45:17Z

1

To reuse same set of ids you could use Table Variable.

DECLARE @T TABLE(ID INT);

INSERT INTO T
SELECT 11111 UNION ALL SELECT 11112 ....

then

SELECT * FROM    
(SELECT Varchar1, 
Varchar2, 
Varchar3, loginUser FROM [LinkedServer].[Database].[dbo].[Table] T1 
INNER JOIN [LinkedServer].[Database].[dbo].[Table] T2
ON T2.INT1 = T1.INT1
WHERE T2.Id IN (select id from @T)
) AS X WHERE
X.Id IN (select id from @T)

edited Jul 10, 2014 at 11:45

answered Jul 10, 2014 at 10:58

Matas Vaitkevicius

61.9k37 gold badges254 silver badges278 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user2232273 Over a year ago

thanks for your advice, but still get the same error

Matas Vaitkevicius Over a year ago

@user2232273 I think I have guessed right this time check update.

user2232273 Over a year ago

I have solved it finally. thanks for your solution. i have write an answer and have mentioned you as an author for the solution. Many thanks. +1 for the key of the solution

gvee · Accepted Answer · 2014-07-10 12:03:57Z

1

Consider using a join instead of a correlated-subquery:

SELECT General.Name
     , General.Street
     , General.Number
     , Users.loginUser
FROM   [LinkedServer].[Database].[dbo].[General]
 LEFT
  JOIN [LinkedServer].[Database].[dbo].[Users]
    ON Users.Id = General.IdUser
WHERE  General.Id IN (11111, 11112, 11113, 11114, 11115)

This will return the loginUser where possible.

answered Jul 10, 2014 at 12:03

gvee

17.2k42 silver badges50 bronze badges

Comments

user2232273 · Accepted Answer · 2014-07-10 11:46:09Z

i have solved it finally.

The Problem was that the subquery haven´t a where condition to extract a specific Id. Without a WHERE Condition the query return more than 1 value.

What i have done:

Thanks for the advice and his solution Author: "LIUFA".

With a combination of his code and some correction of my code i get the solution.

I have add one more column Id and give it an alias. In the subquery i have done the select from the Temp table using a where condition. And its Working. Perfect.

Hope that this solution can help somebody to solved a related issue.

Finally Code:

DECLARE @T TABLE(ID INT);
INSERT INTO @T
Select ReferenceId From MyBigData


SELECT,
A.Id,
Name, 
Street, 
Number,
(SELECT loginUser FROM [LinkedServer].[Database].[dbo].[Users] T1 
INNER JOIN [LinkedServer].[Database].[dbo].[General] T2
ON T2.IdUser = T1.Id
WHERE T2.Id IN (SELECT Id FROM @T WHERE Id = A.Id)
)
FROM [LinkedServer].[Database].[dbo].[General]
WHERE Id IN (SELECT Id FROM @T)

Collectives™ on Stack Overflow

T-SQL - Select Using SubQuery

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related