INNER with条件- Mysql

Question

如果条件为真，我正在尝试进行内连接，但它不起作用，我已经尝试了以下两种方法：

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

或

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

但两者都会产生错误，我想要的是，如果类型等于"vitima“，它会在一个表中执行内部noin，否则在另一个表中。

完整查询：

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

这并不准确，但我认为这将非常接近您正在尝试做的事情：

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)

LEFT JOIN条件强制执行您的规则，而WHERE条件模拟和INNER JOIN，因为它要求存在这些记录。

使用您发布的完整查询，它将如下所示：

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

您不能在join中使用条件，可能更容易使用union，如下所示：

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

我能想到的另一个变体是：

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)