INNER JOIN with Condition - Mysql

Question

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

Can I ask, what's your purpose of the conditional JOIN? Since you aren't actually selecting anything from those tables, are you just trying to make sure that a corresponding record exists? — But those new buttons though.., Jan 12 '19 at 03:04
@billynoah There where I select the fields I also made a "if", do not know if it will work kkkkk, I want to get the name, see: `IF(chat.tipo = 'vitima', vitima.nome_vit, terceiro.nome_ter) as nome` — Woton Sampaio, Jan 12 '19 at 03:06
Ahh, that makes sense. In that case, I think a left join as in my answer might work best, since you might need a column from either table. Another option would be create a subquery - it's hard to give an exact solution without the schema and sample data but I think you're close — But those new buttons though.., Jan 12 '19 at 03:07

score 1 · Answer 1 · answered Jan 12 '19 at 02:54

1

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)

answered Jan 12 '19 at 02:54

Iłya Bursov

23,342
4
33
57

I'll put the complete query for you to see, I tried it here but I can not adapt – Woton Sampaio Jan 12 '19 at 02:59
Take a look now – Woton Sampaio Jan 12 '19 at 03:00

But those new buttons though.. · Accepted Answer · 2019-01-12T03:08:54.557

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

"You have a syntax error in your SQL 'LEFT JOIN vitima ON vitima.id_vit = chat.id_tipo AND chat.tipo = 'vitima' L' at line 17" — Woton Sampaio, Jan 12 '19 at 03:04
It worked :), just take the first `INNER JOIN` there on table `vitima` — Woton Sampaio, Jan 12 '19 at 03:08

INNER JOIN with Condition - Mysql

2 Answers2