Getting only last date of transaction with join

[I-AM-OBODO]

Hello all.
I dont know how to go about this. I have a table (Transactions) that contains transactions of users. Another table (Confirmed) contains details of every confirmed user.

I want to do a select statement that will display all the confirmed user with only the last of their transaction.
But so far all it does is replicate the user and their date of transaction and that is not what i want.

My intention is to get something like:

Firstname    Surname        Date Registered        Last Transaction
andrews        john        12-12-2014        10-10-2015
doe        andy        12-12-2010        12-12-2014

But i'm getting something like:

Firstname    Surname        Date Registered        Last Transaction
andrews        john        12-12-2014        10-10-2015
andrews        john        12-12-2014        10-11-2015
doe        andy        12-12-2010        12-12-2014
doe        andy        12-12-2010        01-12-2014
doe        andy        12-12-2010        12-12-2013

Thanks

$stm=$pdo->query("select * from confirmed left join transaction on confirmed.user_id = transaction.user_id where confirmed.status='confirmed' order by date");

while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {

echo $row['firstname'];
echo $row['surname'];
echo $row['regDate'];
echo $row['lastTrans'];

}

[requinix]

If you're not pulling in data from any other tables, and all you need from the transaction table is the date, then use MAX with a GROUP BY on the user.

SELECT c.firstname, c.surname, c.regDate, MAX(t.lastTrans) AS lastTrans
FROM confirmed c
LEFT JOIN transaction t ON c.user_id = t.user_id
WHERE c.status = 'confirmed'
GROUP BY c.user_id
ORDER BY MAX(t.lastTrans)

[Muddy_Funster]

I'm curious now requinix, I would have done it something along the lines of:

SELECT

  c.firstname,

  c.surname,

  c.regDate,

  trn.mLastTrans

FROM confirmed c

INNER JOIN

(

  SELECT

    user_id,

    MAX(t.lastTrans) AS mLastTrans

  FROM transaction t

  GROUP BY user_id

) AS trn

ON c.user_id = trn.user_id

WHERE c.status = 'confirmed'

ORDER BY c.user_id

 

Asides from being a bit more long winded, would you say there would be any other issues with using a subquery join in that manner? Or is it just a personal thing?

[requinix]

I can't speak much to the performance, but I would expect a non-subquery solution to be faster and more efficient than a subquery solution.

 

If you needed other tables (with a one-many relationship), or if you wanted other aggregate (eg, MAX) data at the same time, then subqueries would be the way to go. Mostly because there aren't really any other alternatives.

[I-AM-OBODO]

If you're not pulling in data from any other tables, and all you need from the transaction table is the date, then use MAX with a GROUP BY on the user.

SELECT c.firstname, c.surname, c.regDate, MAX(t.lastTrans) AS lastTrans
FROM confirmed c
LEFT JOIN transaction t ON c.user_id = t.user_id
WHERE c.status = 'confirmed'
GROUP BY c.user_id
ORDER BY MAX(t.lastTrans)

 

Thanks a zillion dozen times.