more than one row returned by subquery

Question

I have a query where i am using a coalesce to select one of the values. However i get an error saying more than one row returned by a subquery used as an expression. I'm using Postgres v9.3. My code:

 select input1,
 input 2,
 (select (coalesce(tblstuff2.input4, '') || coalesce(tblstuff.input5, ''))
  from tblstuff
  join tblstuff2 on....
  where ...)
input 6,
from..
where...

i need the coalesce to add two columns(strings) from the table and show up as one. For ex:if column input 4 was mobile number and input 5 was phone , atleast one of them should turn up.

Answer 1

It's at least three solutions:

distinct

select distinct (coalesce(tblstuff2.input4, '') || coalesce(tblstuff.input5, ''))
  from tblstuff
  join tblstuff2 on....
  where ...

If join returns the same values.If not, this doesn't work.

limit 1

select (coalesce(tblstuff2.input4, '') || coalesce(tblstuff.input5, ''))
  from tblstuff
  join tblstuff2 on....
  where ...
  limit 1

If join returns different values, but You lost one of them.

string_agg

select string_agg(coalesce(tblstuff2.input4, '') || coalesce(tblstuff.input5, ''), ', ')
  from tblstuff
  join tblstuff2 on....
  where ...

If join returns different values and You want return then all.