Write an additional column to query result with different values everytime

Question

I've been searching for quite a while now and I haven't been able to find an answer for what I was looking. I have the following query:

SELECT DISTINCT o.titulo, o.fecha_estreno
FROM Obra o 
WHERE (o.titulo LIKE '%Barcelona%' AND EXISTS(SELECT p.id_obra FROM Pelicula p WHERE p.id_obra = o.id_obra)) OR EXISTS(SELECT DISTINCT pa.id_obra 
FROM Participa pa
WHERE pa.id_obra = o.id_obra AND EXISTS(SELECT DISTINCT l.nombre FROM Lugar l
    WHERE l.nombre LIKE '%Barcelona%' AND EXISTS(SELECT DISTINCT tl.id_lugar FROM TieneLugar tl
        WHERE tl.id_lugar = l.id_lugar AND tl.id_profesional = pa.id_profesional))) OR EXISTS(SELECT DISTINCT er.id_obra 
        FROM EstaRelacionado er 
            WHERE er.id_obra = o.id_obra AND EXISTS(SELECT DISTINCT k.keyword 
            FROM Keywords k
                WHERE k.id_keyword = er.id_keyword AND k.keyword LIKE '%Barcelona%')); 

What it basically does is it searches for every movie in my database which is related in some way to the city it gets. I wanted to have a third column showing for every result, with the reason the row is showing as a result (for example: TITLE CONTAINS IT, or ACTOR FROM THE MOVIE BORN THERE, etc.)

Thank you for your patience and help!

EDIT: As suggested, here are some examples of output. The column should show just the first cause related to the movie:

TITULO            FECHA_ESTRENO        CAUSE
----------       ----------------    ----------
Barcelona mia         1967             TITLE

Answer 1

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

Answer 2

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 reason
FROM 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_obra
WHERE t1.id_obra is not null
or 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.