CODEWITHSUNDEEP
sqlsql-servert-sql
Art F
Art F

Reputation: 4202

Subquery with multiple results, SQL Server

I have a query that looks like this:

SELECT * 
FROM dbo.document_library_file_attributes 
WHERE my_file_id=
(SELECT my_file_id 
 FROM dbo.document_library_file_attributes 
 WHERE attribute_name='Directory/Key' AND attribute_value LIKE @directory+'%')

I want to the subquery to be able to return multiple results, meaning my_file_id could equal 'directoryA', 'directoryB', and I want the results for all of these. How would I do that?

Upvotes: 2

Views: 6204

Answers (2)

Michael Fredrickson
Michael Fredrickson

Reputation: 37398

I think a JOIN is a better way to express what you're trying to do:

SELECT DISTINCT lfa2.* 
FROM 
    dbo.document_library_file_attributes lfa
    JOIN dbo.document_library_file_attributes lfa2
        ON lfa.my_file_id = lfa2.my_file_id 
WHERE 
    lfa.attribute_name='Directory/Key' 
    AND lfa.attribute_value LIKE @directory+'%'

Upvotes: 3

Oded
Oded

Reputation: 499382

Use IN instead of = for result sets:

SELECT * 
FROM dbo.document_library_file_attributes 
WHERE my_file_id IN
(SELECT my_file_id 
 FROM dbo.document_library_file_attributes 
 WHERE attribute_name='Directory/Key' AND attribute_value LIKE @directory+'%')

It:

Determines whether a specified value matches any value in a subquery or a list.

Upvotes: 7

Related Questions