Jump to content
I-AM-OBODO

Getting only last date of transaction with join

Recommended Posts

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'];

}

Share this post


Link to post
Share on other sites

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)

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.