CODEWITHSUNDEEP
sql-server
Amit Kaushal
Amit Kaushal

Reputation: 439

Executing sub query with case

I have to run a sub query with case statement inside of sub query. When I run the sub query independently it works, but as a sub query it gives following error :

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

This is my query :

select categoryid, categoryname, (select (case when c.ParentCategoryId is NULL then null else c.CategoryName end) as Parent_Category_Name from Categories c) from Categories

My categories table has 3 columns : CategoryId, CategoryName, ParentCategoryId

I am not able to figure out how can I fetch required result.

Upvotes: 2

Views: 2133

Answers (4)

TriV
TriV

Reputation: 5148

Your subquery

(select (case when c.ParentCategoryId is NULL then null else c.CategoryName end) as Parent_Category_Name 
from Categories c)

will be return all categories, it causes problem. As my understand, your query should be:

select c.categoryid, c.categoryname, 
CASE 
     when c.ParentCategoryId IS NULL then '' 
     ELSE pr.categoryname
END as Parent_Category_Name
from Categories c
LEFT JOIN Categories pr ON c.ParentCategoryId = pr.categoryid

Upvotes: 1

DhruvJoshi
DhruvJoshi

Reputation: 17126

Most likely this is what you need to do to your query:

select 
    C1.categoryid, 
    C1.categoryname,
    case 
        when c1.ParentCategoryId is NULL 
        then null 
        else c2.CategoryName 
    end as Parent_Category_Name 
from Categories C1 
left join Categories C2
on C1.ParentCategoryID=C2.CategoryID

your subquery works and helps you find parent_category_name for all categories. you don't need to repeat the whole thing for integrating this logic in other query; simply the select part if sufficient.

More simply you do not need a case evaluation with JOIN.

select 
    C1.categoryid, 
    C1.categoryname,
    c2.CategoryName as Parent_Category_Name 
from Categories C1 
left join Categories C2
on C1.ParentCategoryID=C2.CategoryID

Upvotes: 1

Coder1991
Coder1991

Reputation: 735

Try this.

select categoryid, categoryname, case when c.ParentCategoryId is NULL then null else c.CategoryName end as Parent_Category_Name from Categories

Upvotes: 0

Sandip - Frontend Developer
Sandip - Frontend Developer

Reputation: 14669

Specify Top(1) in your sub query. It seems it return more than one value which not accepted as sub query result.

select categoryid, categoryname, (select TOP(1) (case when c.ParentCategoryId is NULL then null else c.CategoryName end) as Parent_Category_Name from Categories c) from Categories

Upvotes: 0

Related Questions