INNER JOIN with Condition - Mysql

33
January 13, 2019, at 03:40 AM

I'm trying to make an inner join if a condition is true but it does not work, I've tried these 2 ways:

IF chat.tipo = 'vitima' THEN
   INNER JOIN vitima ON vitima.id_vit = chat.id_tipo
ELSE
   INNER JOIN terceiro ON terceiro.id_ter = chat.id_tipo

or

IF(chat.tipo = 'vitima', 
        INNER JOIN vitima ON vitima.id_vit = chat.id_tipo,
        INNER JOIN terceiro ON terceiro.id_ter = chat.id_tipo)

But both give error, what I want is if the type equals "vitima" it does the inner noin in one table, otherwise in the other.

Full query:

SELECT ocorrencia.id_oco, 
        (SELECT GROUP_CONCAT(c.id_oco ORDER BY c.id_oco DESC) FROM ocorrencia as c WHERE c.id_sup_oco = ocorrencia.id_sup_oco) as grouped_ids, 
        ocorrencia.id_sup_oco, 
        chat.id_tipo,
        suporte_oco.data_sup,
        suporte_oco.placa_sup,
        suporte_oco.sinistro_sup,
        suporte_oco.prefixo_sup,
        ocorrencia.id_emp_oco,
        IF(chat.tipo = 'vitima', vitima.nome_vit, terceiro.nome_ter) as nome,
        chat.tipo
        FROM chat 
        INNER JOIN ocorrencia ON ocorrencia.id_oco = chat.id_oco_cha 
        INNER JOIN suporte_oco ON suporte_oco.id_sup = ocorrencia.id_sup_oco
        IF chat.tipo = 'vitima'
            INNER JOIN vitima ON vitima.id_vit = chat.id_tipo
        ELSE
            INNER JOIN terceiro ON terceiro.id_ter = chat.id_tipo
        WHERE chat.id_user = '20' OR chat.id_user_req = '20' GROUP BY (chat.id_oco_cha) ORDER BY chat.data DESC
Answer 1

It's not exact, but I think this would come really close to what you're trying to do:

LEFT JOIN vitima ON vitima.id_vit = chat.id_tipo AND chat.tipo = 'vitima'
LEFT JOIN terceiro ON terceiro.id_ter = chat.id_tipo AND chat.tipo != 'vitima'
...
WHERE (chat.tipo = 'vitima' AND vitima.id_vit IS NOT NULL)
    OR (chat.tipo != 'vitima' AND terceiro.id_ter IS NOT NULL)

The LEFT JOIN conditions enforce your rules and the WHERE condition simulates and INNER JOIN since it requires those records to exist.

With the complete query you posted it would look like this:

SELECT ocorrencia.id_oco, 
    (SELECT GROUP_CONCAT(c.id_oco ORDER BY c.id_oco DESC) FROM ocorrencia as c WHERE c.id_sup_oco = ocorrencia.id_sup_oco) as grouped_ids, 
    ocorrencia.id_sup_oco, 
    chat.id_tipo,
    suporte_oco.data_sup,
    suporte_oco.placa_sup,
    suporte_oco.sinistro_sup,
    suporte_oco.prefixo_sup,
    ocorrencia.id_emp_oco,
    IF(chat.tipo = 'vitima', vitima.nome_vit, terceiro.nome_ter) as nome,
    chat.tipo
FROM chat 
    INNER JOIN ocorrencia ON ocorrencia.id_oco = chat.id_oco_cha 
    INNER JOIN suporte_oco ON suporte_oco.id_sup = ocorrencia.id_sup_oco
    LEFT JOIN vitima ON vitima.id_vit = chat.id_tipo AND chat.tipo = 'vitima'
    LEFT JOIN terceiro ON terceiro.id_ter = chat.id_tipo AND chat.tipo != 'vitima'
WHERE chat.id_user = '20' OR chat.id_user_req = '20'
    AND (
        (chat.tipo = 'vitima' AND vitima.id_vit IS NOT NULL)
        OR (chat.tipo != 'vitima' AND terceiro.id_ter IS NOT NULL)
    )
GROUP BY (chat.id_oco_cha) ORDER BY chat.data DESC
Answer 2

You cannot use conditions in join, probably its easier to use union like:

select * from chat inner join vitima ON 
union all
select * from chat inner join terceiro ON chat.tipo <> 'vitima' AND terceiro.id_ter = chat.id_tipo

another variant I can think of is:

select *
from chat, vitima, terceiro
where
(chat.tipo = 'vitima' AND vitima.id_vit = chat.id_tipo)
OR
(chat.tipo <> 'vitima' AND terceiro.id_ter = chat.id_tipo)
READ ALSO
How do I preserve a UTF8 character set when doing an update into MySQL, from a Google Sheet?

How do I preserve a UTF8 character set when doing an update into MySQL, from a Google Sheet?

I have an app to upload a spreadsheet into my MySQL databaseSome of the fields contain Unicode characters from Northwest Indian languages

50
In C Language, store MySQL row data as variable to avoid segmentation fault with RPi/GPIO stuffs

In C Language, store MySQL row data as variable to avoid segmentation fault with RPi/GPIO stuffs

This is most-likely a stupid beginners questionFor starters, here's my C code [segment]:

49
How to update FK linked to multiple table - Cascade on Update

How to update FK linked to multiple table - Cascade on Update

I have 3 tables which are linked to each other

68
How do I convert a JSON object into a MySQL row?

How do I convert a JSON object into a MySQL row?

I'd like to take a JSON string representing an object from a 3rd party API and insert it into a MySQL tableThe JSON object properties match the table fields 1-to-1

61