Duplicate records in SQL - Oracle

Navigation
1

Good morning, I'm new here and I have a boring problem. Distinct does not solve my problem because the duplication is due to the fact that I had to make the relationship with other tables to look for information. I have a SQL statement that I want to display in SQL Server 2005. 

SELECT ch.idchamado,su.nome AS nomesupervisor,
  e.nome,
  ch.dataabertura,
  ch.datafechamento,
  ta.desctipoassunto
FROM sase_chamado ch
LEFT JOIN vw_app_consulta_escolas e
ON ch.idpj = e.idpessoa_juridica
LEFT JOIN sase_tipoassunto ta
ON ta.idtipoassunto = ch.idtipoassunto
LEFT JOIN sase_escolasusuario eu
ON eu.idpj = ch.idpj
INNER JOIN sase_usuario su
ON eu.idusuario = su.idusuario
WHERE ch.dataabertura BETWEEN to_date('01/01/16', 'dd/mm/yy') AND to_date('30/07/16', 'dd/mm/yy')
ORDER BY su.nome,
  e.nome,
  ta.desctipoassunto

The darn of duplicity is of this type here:

    
asked by anonymous 05.07.2016 / 15:50

1 answer

0

When we have just one line we do not care about the content we use the MIN or MAX and group what is the same. 

SELECT ch.idchamado AS idchamado, 
  MIN( su.nome ) AS nomesupervisor,
  e.nome,
  ch.dataabertura,
  ch.datafechamento,
  ta.desctipoassunto
FROM sase_chamado ch
LEFT JOIN vw_app_consulta_escolas e
ON ch.idpj = e.idpessoa_juridica
LEFT JOIN sase_tipoassunto ta
ON ta.idtipoassunto = ch.idtipoassunto
LEFT JOIN sase_escolasusuario eu
ON eu.idpj = ch.idpj
INNER JOIN sase_usuario su
ON eu.idusuario = su.idusuario
WHERE ch.dataabertura BETWEEN to_date('01/01/16', 'dd/mm/yy') AND to_date('30/07/16', 'dd/mm/yy')
GROUP BY 
  ch.idchamado 
  e.nome,
  ch.dataabertura,
  ch.datafechamento,
  ta.desctipoassunto
ORDER BY su.nome,
  e.nome,
  ta.desctipoassunto
    
05.07.2016 / 16:50
Button with link in MVC5 is not working Directory ./demoCA does not exist in windows openssl