Write an additional column to query result with different values everytime

https://www.postgresql.org/docs/7.4/static/functions-conditional.html

The SQL CASE expression is a generic conditional expression, similar to if/else statements in other languages:

CASE WHEN condition THEN result     [WHEN ...]     [ELSE result] END 

CASE clauses can be used wherever an expression is valid. condition is an expression that returns a boolean result. If the result is true then the value of the CASE expression is the result that follows the condition. If the result is false any subsequent WHEN clauses are searched in the same manner. If no WHEN condition is true then the value of the case expression is the result in the ELSE clause. If the ELSE clause is omitted and no condition matches, the result is null.

Example for your case:

SELECT (CASE WHEN EXISTS(... l.nombre LIKE '%Barcelona%') THEN 'TITLE CONTAINS IT' WHEN <conditon for actor> THEN 'ACTOR WA BORN THERE' WHEN ... END) as reason

Here is one solution.

• Create a subquery for each search condition.
• include the reason in the subqueries' projections
• outer join the subqueries so it doesn't matter which one hist
• filter to make sure that at least one of your subqueries has a positive result
• use coalesce() to get one reason.

I haven't done all your conditions, and I've probably mangled your logic but this is the general idea:

SELECT o.titulo       , o.fecha_estreno       , coalesce(t1.reason, t2.reason) as reasonFROM Obra o      left outer join ( select id_obra, 'title contains it' as reason                        from Obra                        where titulo LIKE '%Barcelona%' ) t1     on t1.id_obra  o.id_obra     left outer join ( select distinct pa.id_obra , 'takes place there' as reason                        from Participa pa                            join TieneLugar tl                            on  tl.id_profesional = pa.id_profesional                             join Lugar l                            on  tl.id_lugar = l.id_lugar                       where l.nombre LIKE '%Barcelona%' ) t2     on t2.id_obra  o.id_obraWHERE t1.id_obra is not nullor t2.id_obra is not null/

coalesce() just returns the first non-null value which means you won't see multiple reasons if you get more than one hit. So order the arguments to put the most powerful reasons first.

Also, you should consider consider using Oracle Text. It's the smartest way to wrangle this sort of keyword searching. Find out more.