Customers who bought in one year but not in another [closed]

Question

Customers who bought in one year but not in another [closed]

Navigation

#1 by (1 votes)
#2 by (0 votes)

1

I need to make a select that brings me which customers I bought in 2016 and did not buy in 2017 and vice versa.

This is my select:

SELECT
tc.'cod-emitente' AS CODIGO,
tc.'nome-emit' AS CLIENTE,
fg.'ano' AS ANO 
FROM tb_clientes tc 
LEFT JOIN faturamento_global fg ON (tc.'cod-emitente' = fg.'cod-emitente') 
WHERE fg.'ano' >= 2016 
GROUP BY fg.'cod-emitente', fg.'ano'

It brings me the following:

CODIGO CLIENTE ANO
14  CLIENTE1    2016
14  CLIENTE1    2017
15  CLIENTE2    2016
15  CLIENTE2    2017
27  CLIENTE3    2016
35  CLIENTE4    2016
35  CLIENTE4    2017

Note that the client with code 27 bought only in 2016 and is a list of clients of this type that I need.

mysql query select

asked by anonymous 26.05.2017 / 18:54

2 answers

0

Could you explain your structure better? Is there a foreign key? Ideally you would have a table with only customer records and another table with purchases, so you could do with LEFT JOIN, for example:

SELECT clientes.id,clientes.nome,compras.ano 

FROM clientes 

LEFT JOIN compras ON (clientes.id = compras.FK_cliente_id)

WHERE ...

26.05.2017 / 19:13

Calculate average of the value of registrations up to 100km sql Trash memory output function inserts in the middle of a list

score 1 · Accepted Answer

You can use the NOT EXISTS clause to check if there is any other billing different from the year in question:

SELECT tc.'cod-emitente' AS CODIGO,
       tc.'nome-emit' AS CLIENTE,
       fg.'ano' AS ANO
  FROM tb_clientes tc
       LEFT JOIN faturamento_global fg ON tc.'cod-emitente' = fg.'cod-emitente'
 WHERE fg.'ano'>= 2016
   AND NOT EXISTS(SELECT 1
                    FROM faturamento_global fg2
                   WHERE fg2.'cod-emitente' = fg.'cod-emitente'
                     AND fg2.'ano' <> fg.'ano')
 GROUP BY fg.'cod-emitente', fg.'ano'

Subqueries with EXISTS or NOT EXISTS

If subquery returns any rows at all, EXISTS subquery is TRUE, and NOT EXISTS subquery is FALSE

Or in free translation:

If the subquery returns any line, EXISTS will be TRUE, and NOT EXISTS will be FALSE