CODEWITHSUNDEEP
sqlpostgresql
Grigor
Grigor

Reputation: 58

How to return more than one rows from subquery used as expression?

bedroom_count is not a single row

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?

Upvotes: 0

Views: 105

Answers (3)

Grigor
Grigor

Reputation: 58

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

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

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.)

Upvotes: 1

Marcin Wroblewski
Marcin Wroblewski

Reputation: 3571

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

Upvotes: 1

Related Questions