norbertpy norbertpy - 3 months ago 6
SQL Question

SQL computed column based on OR clause

If I have the following query:

SELECT *
FROM Flights
WHERE Flights.From='Rome' OR Flights.To='London';


Is there any way I can add a computed column like
matched
to flag which field was matched?

| number | from | to | matched |
|--------|----------|--------|---------|
| 1 | Rome | Munich | "from" |
| 2 | New York | London | "to" |
| 3 | Berlin | London | "to" |

Answer
SELECT *,
    CASE
       WHEN Flights.From='Rome' THEN 'From'
       WHEN Flights.To='London' THEN 'To'
     END as matched
FROM Flights
WHERE Flights.From='Rome' OR Flights.To='London'

You can create a CASE STATEMENT that mimics your logic of your WHERE statement.

SELECT *,
    CASE
       WHEN Flights.From IN ('Rome','London','etc.')
             AND Flights.To IN ('Rome','London','etc.') THEN Flights.From || ', ' || Flights.To
       WHEN Flights.From IN ('Rome','London','etc.') THEN Flights.From
       WHEN Flights.To IN ('Rome','London','etc.') THEN Flights.To
     END as MatchedValue
     ,CASE
       WHEN Flights.From IN ('Rome','London','etc.')
             AND Flights.To IN ('Rome','London','etc.') THEN 'Both'
       WHEN Flights.From IN ('Rome','London','etc.') THEN 'From'
       WHEN Flights.To IN ('Rome','London','etc.') THEN 'To'
     END as MatchedDirection
FROM Flights
WHERE Flights.From='Rome' OR Flights.To='London'

When using case statements the First WHEN condition that is met will be the answer so if BOTH of your conditions are met and you want to see that the first WHEN condition has to look for that possibility.