1

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)) || '%'
Stu
  • 30,392
  • 6
  • 14
  • 33
user1817661
  • 83
  • 1
  • 6
  • The query in `LIKE '%' || (select...) || '%'` will probably return more than 1 row. The `LIKE` expects only 1 string with a pattern. – LukStorms Jan 31 '22 at 20:47
  • SQL Server and PostgrSQL are two different RDBMSs. Based on the syntax I suspect you are targeting PostgreSQL . Is this accurate? – Eduard Uta Jan 31 '22 at 20:48
  • Does this answer your question? [Postgres Error: More than one row returned by a subquery used as an expression](https://stackoverflow.com/questions/21048955/postgres-error-more-than-one-row-returned-by-a-subquery-used-as-an-expression) – Stu Jan 31 '22 at 21:16

1 Answers1

1

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)
     ) 
);
LukStorms
  • 28,916
  • 5
  • 31
  • 45