Using IF/CASE conditioning in SQL

Question

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

• user
• mechanics
• exchanges

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

Answer 1

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