SQL Do not display rows from a duplicate column

Question

Hello guys I’m having a hard time not displaying the items of a duplicate column, in case the column C.CLI_ID, I need to show only the first result. I’ve tried several ways but SQL is not my specialty I’m already without ideas.

SELECT c.cli_id, C.cli_data_cadastro, CT.cont_id, CT.cont_data_vencimento, T.tituloid, T.dataven, T.datapag, T.valorpago, 

CASE 

WHEN T.valorpago <= '0' THEN 'Não Pago'

WHEN ct.cont_data_vencimento  > FORMAT(GETDATE(), 'yyyy-MM-dd') AND T.valorpago > '0' THEN 'Pago'

WHEN ct.cont_data_vencimento < FORMAT(GETDATE(), 'yyyy-MM-dd')  AND T.valorpago > '0' THEN 'Vencido'

END AS situacao 

FROM cliente_rbdigital AS C, contrato_rbdigital AS CT

INNER JOIN titulo_rbdigital AS T ON T.contratoid=CT.cont_id 

WHERE C.cli_id = CT.cont_cliente_id 

ORDER BY C.cli_data_cadastro DESC, situacao DESC


10472   2020-07-31 11:58:44 39486   2020-08-30  39409   2020-08-03  NULL    0.00    Não Pago
10471   2020-07-31 09:57:22 39484   2021-01-27  39407   2020-08-03  2020-07-31  119.40  Pago
10470   2020-07-31 09:35:45 39483   2020-10-29  39406   2020-08-03  NULL    0.00    Não Pago
10469   2020-07-31 05:39:23 39488   2021-01-27  39411   2020-08-01  2020-07-31  119.40  Pago
10469   2020-07-31 05:39:23 39478   2021-01-27  39401   2020-08-03  NULL    0.00    Não Pago
10468   2020-07-30 21:48:26 39473   2020-08-29  39396   2020-08-02  2020-07-30  19.90   Pago
10467   2020-07-30 21:48:21 39472   2021-01-26  39395   2020-08-02  NULL    0.00    Não Pago
10466   2020-07-30 19:54:12 39467   2020-08-29  39391   2020-08-02  2020-07-30  19.90   Pago
10465   2020-07-30 16:54:29 39463   2020-08-29  39386   2020-08-02  NULL    0.00    Não Pago
10464   2020-07-30 09:18:43 39458   2021-07-30  39381   2020-08-02  2020-07-30  238.80  Pago
10463   2020-07-30 07:59:46 39459   2020-08-29  39382   2020-07-31  2020-07-30  9.90    Pago

Answer 1

I don’t know exactly what you want to do with this query, because it’s not bringing duplicates but exactly what you typed in SQL.

If you want to return only customers who have AT LEAST a contract AND AT LEAST a title, see below:

SELECT C.cli_id, 
       C.cli_data_cadastro, 
       CT2.cont_id, 
       CT2.cont_data_vencimento, 
       T2.tituloid, 
       T2.dataven,
       T2.datapag, 
       T2.valorpago, 
       CASE
         WHEN T2.valorpago <= 0 THEN 'Não Pago'
         WHEN CT2.cont_data_vencimento > GETDATE() AND T2.valorpago > 0 THEN 'Pago'
         WHEN CT2.cont_data_vencimento < GETDATE() AND T2.valorpago > 0 THEN 'Vencido'
       END AS situacao

FROM cliente_rbdigital C

CROSS APPLY (
  SELECT TOP 1 CT.cont_id, CT.cont_data_vencimento
  FROM contrato_rbdigital CT
  WHERE CT.cont_cliente_id = C.cli_id
  ORDER BY CT.cont_data_vencimento
) CT2

CROSS APPLY (
  SELECT TOP 1 T.tituloid, T.dataven, T.datapag, T.valorpago
  FROM titulo_rbdigital T
  WHERE T.contratoid = CT2.cont_id
  ORDER BY T.valorpago
) T2

ORDER BY C.cli_data_cadastro DESC, situacao DESC

See also db<>Fiddle by clicking here.

Answer 2

A GROUP BY C.CLI_ID would solve the issue of not displaying more than once the line of a same customer(I think). Although I don’t know if it’s a good idea in your case

10469   2020-07-31 05:39:23 39488   2021-01-27  39411   2020-08-01  2020-07-31  119.40  Pago
10469   2020-07-31 05:39:23 39478   2021-01-27  39401   2020-08-03  NULL    0.00    Não Pago

CT.Cont_id has different values for the two lines and when grouping them will lose this information.