IF record = X, LEFT JOIN in table 1, IF record = Y, LEFT JOIN in table 2

Question

I have a table called "system_signature" containing these fields: - Tipousuario - usuId

This user must save a user’s ID, of course. But this user can have 2 types (Student and Client). The data of these students and customers are stored in another table.

Basically my need would be this:

SELECT * FROM sistema_assinatura ass 
LEFT JOIN
    (CASE assTipoUsuario = 'A'
        THEN sistema_aluno a ON ass.usuId = a.aluId
    CASE assTipoUsuario = 'C'
        THEN sistema_cliente c ON ass.usuId = c.cliId) 

But obviously that didn’t work.

Is something like this possible? If so, how to proceed? If the type of student recorded in the record is A, do a JOIN in the table "system_student". If type C, then JOIN should be in the table "system_client".

I tried that way too:

SELECT * FROM sistema_assinatura ass 
(
    CASE WHEN assTipoUsuario = 'A' THEN LEFT JOIN sistema_aluno a ON ass.usuId = a.aluId
    CASE WHEN assTipoUsuario = 'C' THEN LEFT JOIN sistema_cliente c ON ass.usuId = c.cliId
)

Answer 1

You have to do the Join with both, and the data you select what you need:

SELECT
ass.*,
(CASE WHEN ass.TipoUsuario = 'A' THEN 
   a.[campo]
 WHEN ass.TipoUsuario = 'C' THEN 
   c.[campo]
 ELSE '?' END) as tipoUsuario 
FROM sistema_assinatura ass
LEFT JOIN sistema_aluno a ON ass.usuId = a.aluId 
LEFT JOIN sistema_cliente c ON ass.usuId = c.cliId

OR

Utilize Union:

SELECT
ass.*,
a.[campo]
FROM sistema_assinatura ass
LEFT JOIN sistema_aluno a ON ass.usuId = a.aluId 
WHERE ass.TipoUsuario = 'A'

union

SELECT
ass.*,
c.[campo]
FROM sistema_assinatura ass
LEFT JOIN sistema_cliente c ON ass.usuId = c.cliId 
WHERE ass.TipoUsuario = 'C'