Joining tables

Question:

I have three tables, one called CRM_PROCESSO , another called CRM_PROCESSO_VARIAVEL and the last one called CRM_PROCESSO_ATIVIDADE , both have the variable idprocesso in common.

What I need is for the command to bring up the IDPROCESSO , USUARIO , DATAINICIO , DATATERMINO from the CRM_PROCESSO table, the DESCRICAO field from the CRM_PROCESSO_ATIVIDADE table and the valoratual field from the CRM_PROCESSO_VARIAVEL table

I managed to make the command, but with separate data.

I ran the following command, and brought the field IDPROCESSO , USUARIO , DATAINICIO , DATATERMINO table CRM_PROCESSO , the field DESCRICAO Table CRM_PROCESSO_ATIVIDADE when both have 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 could do the following command, bringing the field valoratual table 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 commandos together.

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

Can anyone help me?

Answer:

If the relationship is 1 para 1 between the three tables, then a normal JOIN between them would suffice, but from the comments it seems that the relationship is 1 para N and the filters in the tables force a 1 para 1 relationship. In this case you can filter in JOIN or create SUBQUERY with built-in filters.

The version with filter in the JOIN is this:

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 is this:

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 
Scroll to Top