3 Replies Latest reply on Jul 23, 2018 10:00 PM by Zhouyi Zhang

    Filtering clients where exist in other table

    marcelo Zanetta

      I've been struggling to find the solution for this:

       

      1)  Table with sells from store A

       

      Client_ID , charge, date

      5674, 2000, 2018-04-02

      5674, 500, 2018-04-02

      5673, 2000, 2018-04-02

       

      2 ) Table with sells from store B

       

      Client_ID , charge, date

      5674, 2001, 2018-04-02

      5023, 1000, 2018-04-02

       

       

      I need to calculate unique Clients and the sum of their charges aggregates by month (using field date) from table A , but only if they don't have purchases on table B (another store).

      In SQL should be something like this:

       

      select client_id, sum(charge)

      from tableA

      where date_format(date,'%Y-%m') = '2017-02' and client_id not in(select distinct client_id from TableB where date_format(date,'%Y-%m') = '2017-02')

      group by cliend_id

       

      Does anyone want to start trying to solve it?

       

      Thanks you!

      Regards.