CODEWITHSUNDEEP
sqlpostgresql
user1817661
user1817661

Reputation: 83

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

I don't understand what the problem is, I get this error:

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

select * 
from "History" 
WHERE "Message" LIKE '%' || (select "Name" 
                             from  public."Campaign"
                             where "Employ" IN (SELECT * 
                                                FROM (                                                                 
                                                  SELECT "Employ" 
                                                  FROM public."Sub"
                                                  where  "Company" ilike '%XX%'limit 1
                                                )
                                                union all
                                                (SELECT "Employ" 
                                                 FROM public."Sub"
                                                 where "Company" not ilike '%XX%'limit 1)
                                                ) as t)) || '%'

Upvotes: 1

Views: 583

Answers (1)

LukStorms
LukStorms

Reputation: 29647

Instead of trying to LIKE multiple names, it could LIKE ANY of the names.

select * 
from "History" 
where "Message" LIKE ANY (
     select '%'||"Name"||'%'
     from  public."Campaign"
     where "Employ" IN (
           (select "Employ" from public."Sub" 
            where "Company" ilike '%XX%' limit 1) 
           union all
           (select "Employ" from public."Sub"
            where "Company" not ilike '%XX%' limit 1)
     ) 
);

Upvotes: 1

Related Questions