Delete duplicated values-CodePudding

I have a table called ac_eeb which has almost 3000 records. I would like to add the column TIPCOL to this table which one i can make a join function to take from ac_sep. But, when i make the join, the result gives me more records than the necessary and i saw that it is giving me duplicated values... How can i remove the duplicated values by the initial code i was using ?

SELECT
       emp_codigo as NUMEMP /* Código da Empresa SIM*/
       , CASE
       when categoriaesocial = '101' then 'CLT'
       when categoriaesocial = '901' then 'Estagiário'
       when categoriaesocial = '103' then 'Aprendiz'
       when categoriaesocial = '722' then 'Estatutário'
       end
       as TIPCOL /* Tipo Colaborador Sim*/
       , epg_codigo as NUMCAD /* Cadastro do Colaborador SIM*/
       , dtinicial as INIETB /* Data de Início da Estabilidade SIM */
       , dtfinal as FIMETB /* Data Final da Estabilidade    NÃO */
       , esb_codigo as CODETB /* Código da Estabilidade SIM */
FROM 
      (SELECT  stg.ac_eeb.emp_codigo, stg.ac_eeb.esb_codigo, stg.ac_eeb.epg_codigo, 
             stg.ac_eeb.dtinicial, stg.ac_eeb.dtfinal, stg.ac_sep.categoriaesocial
       FROM stg.ac_eeb
       JOIN stg.ac_sep
       ON ac_eeb.epg_codigo = ac_sep.epg_codigo) AS Estabilidades;

CodePudding user response：

Join you used in this query does not have any where condition. Due to this, the output of the query will be cartesian product of table ac_eeb and ac_sep which would contain duplicate values. To avoid this, use where clause to filter out unnecessary data

CodePudding user response：

The duplicates would have been caused by ac_sep having more than one record for some epg_codigo values. If you know that ac_sep.categoriasocial is the same for every row with the same epg_codigo, then you can use a subquery to get unique values before you do the join; like this:

SELECT
       emp_codigo as NUMEMP /* Código da Empresa SIM*/
       , CASE
       when categoriaesocial = '101' then 'CLT'
       when categoriaesocial = '901' then 'Estagiário'
       when categoriaesocial = '103' then 'Aprendiz'
       when categoriaesocial = '722' then 'Estatutário'
       end
       as TIPCOL /* Tipo Colaborador Sim*/
       , epg_codigo as NUMCAD /* Cadastro do Colaborador SIM*/
       , dtinicial as INIETB /* Data de Início da Estabilidade SIM */
       , dtfinal as FIMETB /* Data Final da Estabilidade    NÃO */
       , esb_codigo as CODETB /* Código da Estabilidade SIM */
FROM 
      (SELECT  stg.ac_eeb.emp_codigo, stg.ac_eeb.esb_codigo, stg.ac_eeb.epg_codigo, 
             stg.ac_eeb.dtinicial, stg.ac_eeb.dtfinal, stg.ac_sep.categoriaesocial
       FROM stg.ac_eeb
       JOIN (select distinct 
                      epg_codigo
                    , categoriaesocial
             from stg.ac_sep
            ) as ac_sep
       ON ac_eeb.epg_codigo = ac_sep.epg_codigo) AS Estabilidades;