Junction of tables

Question

I got three tables a call CRM_PROCESSO, another call CRM_PROCESSO_VARIAVEL and the last call CRM_PROCESSO_ATIVIDADE, both have the variable idprocesso in common.

What I need is for command to bring the field IDPROCESSO, USUARIO, DATAINICIO, DATATERMINO table CRM_PROCESSO,the field DESCRICAO table CRM_PROCESSO_ATIVIDADEand the countryside valoratualtable CRM_PROCESSO_VARIAVEL

I was able to do the command, but with the separate data.

I turned the remote down, and I brought the field IDPROCESSO, USUARIO, DATAINICIO, DATATERMINO table CRM_PROCESSO,the field DESCRICAO table CRM_PROCESSO_ATIVIDADE, when they both have the IDPROCESSO equal.

    select crm_processo.idprocesso,
       crm_processo.usuario,
       crm_processo.datainicio,
       crm_processo.datatermino,
       crm_processo_atividade.descricao  
   from crm_processo join crm_processo_atividade on (crm_processo_atividade.idprocesso = crm_processo.idprocesso and crm_processo_atividade.idatividade = crm_processo.idatividadeatual) where status = 1 and idprocedimento = 34 and idatividadeatual <>2  

And I managed to make the command below, bringing the field valoratualtable CRM_PROCESSO_VARIAVEL (I used an example with IDPROCESSO = 39)

SELECT  cpv.descricao, 
cpv.valoratual,
(CASE CPV.DESCRICAO
     WHEN '/*MOTIVOCANCELAMENTO*/' THEN 'Motivo Cancelamento'
     END)  from crm_processo_variavel cpv  where idprocesso = 3089 AND CPV.DESCRICAO IN ('/*MOTIVOCANCELAMENTO*/')

But honestly, I don’t know how to put the two commands together.

The process x activity ratio is 1 to 1. And variable x process also 1 to 1.

Someone can help me ?

Answer 1

If the relationship is 1 para 1 between the three tables, then a JOIN normal between them, but from the comments it seems that the relationship is 1 para N and the filters on the tables force a relationship 1 para 1. In this case you can make the filter on JOIN or creating SUBQUERY with internal filters.

The filter version on JOIN that’s the one:

SELECT P.idprocesso,
       P.usuario,
       P.datainicio,
       P.datatermino,
       PA.descricao AS pa_descricao,
       PV.valoratual,  
       CASE PV.descricao 
         WHEN '/*MOTIVOCANCELAMENTO*/' THEN 'Motivo Cancelamento'     
         END AS pv_descricao
FROM crm_processo AS P 
LEFT JOIN crm_processo_atividade AS PA
     ON PA.idprocesso = P.idprocesso
     AND idatividade = P.idatividadeatual
     AND idatividade <> 2
LEFT JOIN crm_processo_variavel AS PV
     ON PV.idprocesso = P.idprocesso
     AND PV.descricao IN ('/*MOTIVOCANCELAMENTO*/')
WHERE 
     P.status = 1 
     AND P.idprocedimento = 34 

The version with SUBQUERY that’s the one:

SELECT P.idprocesso,
       P.usuario,
       P.datainicio,
       P.datatermino,
       PA.descricao AS pa_descricao,
       PV.descricao AS pv_descricao,
       PV.valoratual  
FROM crm_processo P 
LEFT JOIN (SELECT descricao FROM crm_processo_atividade 
           WHERE idprocesso = P.idprocesso    
           AND idatividade = P.idatividadeatual
           AND idatividade <> 2 ) AS PA           
     ON PA.idprocesso = P.idprocesso
LEFT JOIN (SELECT valoratual, 
             CASE descricao 
             WHEN '/*MOTIVOCANCELAMENTO*/' THEN 'Motivo Cancelamento'     
             END AS descricao
           FROM crm_processo_variavel 
           WHERE idprocesso = P.idprocesso 
           AND descricao IN ('/*MOTIVOCANCELAMENTO*/') ) AS PV
     ON PV.idprocesso = P.idprocesso
WHERE 
     P.status = 1 
     AND P.idprocedimento = 34