MySQL Group Join Table

Question

I have below three SQL statement and I want to select out like below, I tried but not success.

Need some help.

Output:

member_id, balance, firstname, lastname, LastPurchase, LastOrde


SELECT c.member_id
     , c.firstname
     , c.lastname
     , m.balance 
  FROM member m
     , customer c
 where  m.member_id =  c.member_id
 order 
    by m.member_id


SELECT member_id, max(date) as LastPurchase 
  FROM purchase
 GROUP 
    BY member_id

SELECT member_id, max(date) as LastOrder   
  FROM ordert
 GROUP 
    BY member_id

Answer 1

You can join these statements -

SELECT c.member_id, c.firstname, c.lastname, m.balance, p.LastPurchase, o.LastOrder   
FROM member m
join customer c on m.member_id =  c.member_id
left join (SELECT member_id, max(date) as LastPurchase 
      FROM purchase
      GROUP BY member_id) p on p.member_id = m.member_id
left join (SELECT member_id, max(date) as LastOrder   
      FROM ordert
      GROUP BY member_id) o on o.member_id = m.member_id
order by m.member_id

Answer 2

You can join the aggregate queries. The JOIN ... USING syntax comes handy here, since all join column names are the same:

SELECT c.member_id, c.firstname, c.lastname, m.balance, p.last_purchase, o.last_purchase
FROM member m
INNER JOIN customer c USING(member_id)
INNER JOIN (
    SELECT member_id, max(date) last_purchase FROM purchase GROUP BY member_id
) p USING(member_id)
INNER JOIN (
    SELECT member_id, max(date) last_order FROM order GROUP BY member_id
) o USING(member_id)
ORDER BY c.member_id

Important: your original query uses implicit, old-shool joins (with a comma in the from clause) - this syntax fell out of favor more than 20 years ago and its use is discourage, since it is harder to write, read, and understand.

One of the many benefits of using explicit joins here is that you can easily change the INNER JOINs to LEFT JOINs if there is a possibility that a member has no purchase or no order at all.