CODEWITHSUNDEEP
sql-servert-sql
user2232273
user2232273

Reputation: 4964

T-SQL - Select Using SubQuery

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.

Upvotes: 0

Views: 874

Answers (3)

gvee
gvee

Reputation: 17161

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.

Upvotes: 1

user2232273
user2232273

Reputation: 4964

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)

Upvotes: 0

Matas Vaitkevicius
Matas Vaitkevicius

Reputation: 61401

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)

Upvotes: 1

Related Questions