Join tables

Question

Join tables

Navigation

#1 by (0 votes)

2

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

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

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

I circled the command below, and brought the field CRM_PROCESSO_VARIAVEL , IDPROCESSO , USUARIO , DATAINICIO from table DATATERMINO , field CRM_PROCESSO from table DESCRICAO , when both have CRM_PROCESSO_ATIVIDADE 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 was able to do the following command, bringing the field IDPROCESSO of the table valoratual (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 do not know how to join the two command.

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

Can anyone help me?

join tabela

asked by anonymous 21.09.2018 / 15:45

1 answer

Is there any problem in using Unicode characters for identifiers in code? Close all open jframes when opening a new one

score 0 · Accepted Answer

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

The filter version in 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