Customers who bought in one year but not another

Asked

Viewed 368 times

1

I need to make a select that brings me which customers 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`

He 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 customer with code 27 bought only in 2016 and is a list with customers of this type that I need.

mysql query select

  • You need to clarify your question a little by showing, for example, situations where your condition is satisfied.

  • You cannot put the data in this table in your question to facilitate understanding?

  • I executed this way, however, brought the result I put in the initial post, now I need to separate which customer bought in one year and not another.

2 answers

1


You can use the clause NOT EXISTS to verify that there is a different billing than 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 a 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

0

Could you explain your structure better? Is there a foreign key? The ideal would be to have a table only with customer registration 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 ...

Browser other questions tagged mysql query select

You are not signed in. Login or sign up in order to post.