How to return more than one row from subquery used as expression-CodePudding

select case 
         when p.property_type ='APARTMENT_COMMUNITY' 
           then (select fp.bedroom_count 
                 from floor_plans fp 
                 where fp.removed = false 
                 and fp.property_id=p.id) 
         else (select pu.bedroom_count 
               from property_units pu 
               where pu.removed = false 
               and pu.property_id=p.id) 
        end 
from properties p 
where p.id =550

I have this,bedroom_count is not a single row,so it gives this error

ERROR: more than one row returned by a subquery used as an expression

I need to get that result in that case is there any other solution for this?

CodePudding user response：

The error comes from the fact that either first or second subquery return more than 1 row for given property_id (550). From your comments

I want all of them as result

I'm guessing that what you need is left join with both tables. Try this

select p.property_type, coalesce(fp.bedroom_count, pu.bedroom_count) as bedroom_count
  from properties p
  left join floor_plans fp 
    on p.property_type = 'APARTMENT_COMMUNITY' and fp.removed = false and fp.property_id = p.id
  left join property_units pu
    on p.property_type <> 'APARTMENT_COMMUNITY' and pu.removed = false and pu.property_id = p.id
 where p.id = 550

CodePudding user response：

It sounds like you really want to join the tables. As you want the bedroom counts from one table or the other, though, you would have to outer join the tables.

select p.*, coalesce(fp.bedroom_count, pu.bedroom_count) as bedroom_count
from properties p
left join floor_plans fp on p.property_type = 'APARTMENT_COMMUNITY' 
                         and fp.property_id = p.id
                         and fp.removed = false 
left join property_units pu on p.property_type <> 'APARTMENT_COMMUNITY' 
                            and pu.property_id = p.id
                            and pu.removed = false 
where p.id = 550
order by p.id;

Or use UNION ALL:

select p.*, fp.bedroom_count
from properties p
join floor_plans fp on fp.property_id = p.id and fp.removed = false 
where p.id = 550
and p.property_type = 'APARTMENT_COMMUNITY'
union all
select p.*, pu.bedroom_count
from properties p
join property_units pu on pu.property_id = p.id and pu.removed = false 
where p.id = 550
and p.property_type <> 'APARTMENT_COMMUNITY'
order by p.id;

(If property_type can be null, these queries will need some adjustment to deal with this.)

CodePudding user response：

select  case 
            when p.property_type ='APARTMENT_COMMUNITY' 
                then (  
                    select  array_agg(distinct fp.bedroom_count) 
                    from    floor_plans fp 
                    where   fp.removed = false 
                    and     fp.property_id=p.id ) 
            else (
                    select  (array_agg(distinct pu.bedroom_count)) 
                    from    property_units pu 
                    where   pu.removed = false 
                    and pu.property_id=p.id ) 
        end 
from    properties p 
where   p.id =550

this is the answer to my problem in case someone needs it