CODEWITHSUNDEEP
mysqlsql
Jaime Quintana
Jaime Quintana

Reputation: 57

Using IF/CASE conditioning in SQL

I have a question regarding how you use the IF statement in SQL, so I have 3 tables

The fields in each of tables are as follows

 user        mechanics      exchanges
------      ----------      ---------
name         name           id_user
id_user      id_mecha       id_mecha 
                            message

I want to use a condition like the following one in which I am going to select the name of the user or the mechanic with their corresponding message if their id matches the one that exchanges has for each one of them (user or mechanic)

SELECT CASE  
         WHEN mechanics.id_mecha = exchanges.id_mecha  
            THEN  mechanics.name, exchanges.message
         WHEN users.id_user = exchanges.id_user 
            THEN users.name, exchanges.message
FROM users 
JOIN mechanics 
JOIN exchanges

Upvotes: 3

Views: 526

Answers (3)

Barmar
Barmar

Reputation: 780974

The double JOIN will produce a full cross product between the mechanics and users. This is probably not what you want. You should use a single join with each table, and then combine them with UNION.

SELECT m.name, e.message
FROM mechanics AS m
JOIN exchanges AS e ON m.id_mecha = e.id_mecha

UNION

SELECT u.name, e.message
FROM users AS u
JOIN exchanges AS e ON m.id_user = e.id_user

Upvotes: 4

void
void

Reputation: 7880

in fact you need a JOIN with case statement (assuming an specific message can be only for a user or for a specific mechanic- not for both at the same time in one row):

SELECT case 
       when e.id_user is null then m.name
       when e.id_mecha is null then u.name
       else null end,
       e.message
from exchanges e join users u on e.id_user=u.id_user
     join mechanics m on e.id_mecha=m.id_mecha

Upvotes: 0

Ram
Ram

Reputation: 3091

If I understand correctly you need to END the CASE in SELECT and you need to have ON for JOIN like below

SELECT CASE  
         WHEN mechanics.id_mecha = exchanges.id_mecha  THEN  mechanics.name
         WHEN users.id_user = exchanges.id_user THEN users.name 
       END,
       exchanges.message
FROM  users u
JOIN mechanics m ON users.id_user = exchanges.id_user
JOIN exchanges e ON mechanics.id_mecha = exchanges.id_mecha

Upvotes: 0

Related Questions