I have a subquery in SELECT and sometimes it returns more than one row. I want to solve the problem like this:
- when more than 1 row write a string like ‘multi’
- otherwise use the value description from comment table
Query:
select s.id, (select description from comment c where c.id = s.id) from student s;
thanks.
Advertisement
Answer
You can join, aggregate, and use a conditional expression:
select s.id, case when min(c.description) <> max(c.description) then 'multi' else min(c.description) end description from student s left join comment c on c.id = s.id group by s.id
You can also do this with a subquery, which avoids outer aggregation (it is handy if you need more columns from students
):
select s.*, ( select case when min(c.description) <> max(c.description) then 'multi' else min(c.description) end from comment c where c.id = s.id ) description from student s