CODEWITHSUNDEEP
mysqlsql
AdamMes
AdamMes

Reputation: 1

MYSQL: retrieve all the users details with the details of the first sale for each user by date, 2 tables

I'm trying to retrieve all the users details with the details of the first sale for each user by date.

Mysql tables: enter image description here

my code:

SELECT u.id,u.name,u.email,s.amount,s.date 
FROM users u 
INNER JOIN sales s ON u.id=s.user_id 
ORDER BY u.id,s.date

what its return:

[{"id":"1","name":"aaa","email":"[email protected]","amount":"5600","date":"2019-11-11"},{"id":"1","name":"aaa","email":"[email protected]","amount":"3000","date":"2020-01-08"},{"id":"2","name":"bbb","email":"[email protected]","amount":"6000","date":"2019-12-15"},{"id":"2","name":"bbb","email":"[email protected]","amount":"1000","date":"2020-06-05"},{"id":"3","name":"ccc","email":"[email protected]","amount":"7500","date":"2019-09-02"},{"id":"4","name":"ddd","email":"[email protected]","amount":"5000","date":"2019-03-12"},{"id":"4","name":"ddd","email":"[email protected]","amount":"4000","date":"2020-04-21"}]

I want to get the earliest date row of each id,like that:

[{"id":"1","name":"aaa","email":"[email protected]","amount":"5600","date":"2019-11-11"},{"id":"2","name":"bbb","email":"[email protected]","amount":"6000","date":"2019-12-15"},{"id":"3","name":"ccc","email":"[email protected]","amount":"7500","date":"2019-09-02"},{"id":"4","name":"ddd","email":"[email protected]","amount":"5000","date":"2019-03-12"}]>

help someone?

Upvotes: 0

Views: 155

Answers (1)

Brian DeMilia
Brian DeMilia

Reputation: 13248

You can use a query that finds the first sales ID for each user as an inline view (a query in the from clause) and join that inline view to the two tables in question. Like this:

select    u.*,
          s.amount,
          s.date
from      users                         u
     join (
            select    user_id,
                      min(date) as min_date
            from      sales
            group by  user_id
          )                             v
     join sales                         s
       on u.user_id = v.user_id
      and s.date = v.min_date;

Upvotes: 0

Related Questions