I-AM-OBODO Posted January 11, 2016 Share Posted January 11, 2016 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 Transactionandrews john 12-12-2014 10-10-2015doe andy 12-12-2010 12-12-2014But i'm getting something like:Firstname Surname Date Registered Last Transactionandrews john 12-12-2014 10-10-2015andrews john 12-12-2014 10-11-2015doe andy 12-12-2010 12-12-2014doe andy 12-12-2010 01-12-2014doe andy 12-12-2010 12-12-2013Thanks $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']; } Quote Link to comment Share on other sites More sharing options...
Solution requinix Posted January 11, 2016 Solution Share Posted January 11, 2016 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) Quote Link to comment Share on other sites More sharing options...
Muddy_Funster Posted January 11, 2016 Share Posted January 11, 2016 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? Quote Link to comment Share on other sites More sharing options...
requinix Posted January 11, 2016 Share Posted January 11, 2016 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. Quote Link to comment Share on other sites More sharing options...
I-AM-OBODO Posted January 11, 2016 Author Share Posted January 11, 2016 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.