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

Question

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

Navigation

#1 by (3 votes)

0

I have a table called "system_name" containing these fields: - User Type - usuId

This user should store a user's ID, obviously. But this user can have 2 types (Student and Client). The data for these students and clients is 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 did not work.

Is something like this possible? If yes, how to proceed? If the student type recorded in the record is A, it makes a JOIN in the "student_table" table. If it is type C, then the JOIN should be in the table "system_client".

I also tried this way:

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
)

mysql

asked by anonymous 06.07.2018 / 18:50

1 answer

How to detect when scrolling an element comes to the end without jQuery? How to create an accumulator

score 3 · Accepted Answer

You have to do the join with both, and the data you select the one 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

OU

Use 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'