CODEWITHSUNDEEP
sqlsql-servert-sql
Rok Potočnik
Rok Potočnik

Reputation: 137

SQL subquery returned more than 1 result

I have a query, when I check if Filters are NULL then I Select all, but if they are not null I select only objects that have those filters. The query is as follows.

SELECT * 
FROM [PointOfInterest]
WHERE [PointOfInterest].[Id] IN (CASE
                                    WHEN @filters IS NULL THEN [PointOfInterest].[Id]
                                    WHEN @filters IS NOT NULL THEN (SELECT DISTINCT [PointOfInterestIcon].[PointOfInterestId]
                                            FROM  [PointOfInterestIcon]
                                            WHERE [IconId] IN (SELECT [Id] FROM OPENJSON(@filters)WITH([Id] NVARCHAR(255))))
                                    END)

If I delete CASE statement and put only the SELECT in the IN the query works. Any ideas how to fix this?

Upvotes: 2

Views: 63

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133400

You have a inner select that return more then a row .. you colud use TOP 1 for limit this result 

SELECT * 
FROM [PointOfInterest]
WHERE [PointOfInterest].[Id] IN (
    CASE
            WHEN @filters IS NULL THEN [PointOfInterest].[Id]
            WHEN @filters IS NOT NULL THEN (
                    SELECT TOP 1 DISTINCT [PointOfInterestIcon].[PointOfInterestId]
                    FROM  [PointOfInterestIcon]
                    WHERE [IconId] IN (
                    SELECT [Id] FROM OPENJSON(@filters)WITH([Id] NVARCHAR(255))))
    END)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271151

Just use regular boolean logic:

SELECT * 
FROM [PointOfInterest]
WHERE @filters IS NULL OR
      [PointOfInterest].[Id] IN (SELECT poi.[PointOfInterestId]
                                 FROM [PointOfInterestIcon] poi
                                 WHERE poiIconId IN (SELECT [Id] FROM OPENJSON(@filters) WITH ([Id] NVARCHAR(255)))
                                );

Upvotes: 5

Related Questions