SQL Query return extra column depending of condition

Question

I want to make a query which will add a column depending of what condition is meet(I have tried with sequelize without obtaining the result that I wanted) therefore I want to try with plain SQL because It does not have as many limitations.

EXAMPLE: SELECT * FROM db.messages WHERE user_id = userID OR $contacts.user_id$ = userID

If the first condition is meet I want to add a column like:

SELECT *,'type' as 'Inbox' FROM db.messages

Second condition:

SELECT *,'type' as 'Send' FROM db.messages

I have not manage to implement it with a CASE...THEN

I would appreciette any comment.

SQL CASE STatement:

SELECT *,
CASE
    WHEN user_id = userID THEN 'Inbox'
    WHEN $contacts.user_id$ = userID THEN 'Send'
    ELSE NULL
END AS type
FROM db.messages;

You can't add different columns for different records neither in SQL nor using Sequelize. If these conditions are outside of query itself then you can achieve this in Sequelize — Anatoly, Dec 27 '20 at 16:36
Can you supply the case statement you tried for review. I don't see why you couldn't do what you are suggestion with a case. The only tricky part would be, what if the user sent the email to themselves, what would you want returned? — SteveB, Dec 27 '20 at 16:41
I was expecting it to be posible because inside the Select query in would add a new column like in https://stackoverflow.com/questions/3769702/how-to-create-a-new-column-in-a-select-query I have tried it in MySQLWorkbench — Lluis Semper, Dec 27 '20 at 16:46
When the user sends the messages to themself It would be type Inbox — Lluis Semper, Dec 27 '20 at 16:47

score 1 · Accepted Answer · answered Dec 27 '20 at 17:43

The syntax for the case statement seems to be invalid, check the documentation. Instead of this:

select
    case
        when a = x then 'a' as result
        when b = x then 'b' as result
        else null
    end as y
from table_name

Try this:

select
    case
        when a = x then 'a'
        when b = x then 'b'
    end as 'result'
from table_name

or even simpler:

select
    case x
        when a then 'a'
        when b then 'b'
    end as 'result'
from table_name

OH WOW, thank you very much!! Due my lack of experience I was trying to overcomplicate things and missed some basic concepts. — Lluis Semper, Dec 27 '20 at 17:50

SQL Query return extra column depending of condition

1 Answers1