CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Haresh Ambaliya
Haresh Ambaliya

Reputation: 711

WHERE CASE WHEN statement with Exists

I am creating a SQL query having WHERE CASE WHEN statement. I am doing something wrong and getting error.

My SQL statement is like

DECLARE @AreaId INT = 2
DECLARE @Areas Table(AreaId int)

INSERT INTO @Areas SELECT AreaId
FROM AreaMaster
WHERE CityZoneId IN (SELECT CityZoneId FROM AreaMaster WHERE AreaId = @AreaID)

SELECT * 
FROM dbo.CompanyMaster
WHERE AreaId IN
     (CASE WHEN EXISTS (SELECT BusinessId
                        FROM dbo.AreaSubscription
                        WHERE AreaSubscription.BusinessId = CompanyMaster.BusinessId) 
             THEN @AreaId 
             ELSE (SELECT [@Areas].AreaId FROM @Areas)
      END)

I am getting error as

Msg 512, Level 16, State 1, Line 11
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Please help to successfully run query. My logic is to checking for conditional AreaId in (statement) for each row.

I want to select the row only when

  1. company has subscription entry into AreaSubscription for specific area passed by @AreaId
  2. table AreaSubscription does not have subscription entry then evaluate AreaId in (SELECT [@Areas].AreaId FROM @Areas)

Upvotes: 26

Views: 150252

Answers (4)

Kosala W
Kosala W

Reputation: 2143

Try this:

DECLARE @AreaId INT = 2
DECLARE @Areas Table(AreaId int)

INSERT INTO @Areas SELECT AreaId
FROM AreaMaster
WHERE CityZoneId IN (SELECT CityZoneId FROM AreaMaster WHERE AreaId = @AreaID)

INSERT INTO @Areas 
SELECT AreaSubscription.BusinessId
    FROM dbo.AreaSubscription INNER join CompanyMaster
    ON AreaSubscription.BusinessId = CompanyMaster.BusinessId   

SELECT * FROM dbo.CompanyMaster
WHERE AreaId IN (SELECT DISTINCT [@Areas].AreaId FROM @Areas)

Upvotes: 0

Nithesh Narayanan
Nithesh Narayanan

Reputation: 11775

This may help you.

SELECT * FROM dbo.CompanyMaster
WHERE AreaId=
(CASE WHEN EXISTS (SELECT BusinessId
                   FROM dbo.AreaSubscription
                   WHERE AreaSubscription.BusinessId = CompanyMaster.BusinessId) 
      THEN @AreaId ELSE AreaId END)
AND  AreaId IN (SELECT [@Areas].AreaId FROM @Areas)

One more solution is

SELECT * FROM dbo.CompanyMaster A 
LEFT JOIN @Areas B ON A.AreaId=B.AreaID
WHERE A.AreaId=
(CASE WHEN EXISTS (SELECT BusinessId
                   FROM dbo.AreaSubscription
                   WHERE AreaSubscription.BusinessId = CompanyMaster.BusinessId) 
      THEN @AreaId ELSE B.AreaId END)
)

Upvotes: 33

bvr
bvr

Reputation: 4826

Try this

DECLARE @AreaId INT = 2
DECLARE @Areas Table(AreaId int)

INSERT INTO @Areas 
SELECT AreaId FROM AreaMaster
WHERE CityZoneId IN (SELECT CityZoneId FROM AreaMaster WHERE AreaId = @AreaID)

    IF EXISTS (SELECT BusinessId
        FROM dbo.AreaSubscription
        WHERE AreaSubscription.BusinessId = CompanyMaster.BusinessId) 
    BEGIN
        SELECT * FROM dbo.CompanyMaster
        WHERE AreaId IN (@AreaId) 
    END
    ELSE
    BEGIN
        SELECT * FROM dbo.CompanyMaster
        WHERE AreaId IN (SELECT [@Areas].AreaId FROM @Areas)
    END

Upvotes: 0

Sunil Tandon
Sunil Tandon

Reputation: 151

Try putting SELECT top 1 [@Areas].AreaId FROM @Areas if it solves the issue..

Upvotes: 0

Related Questions