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!