how to show each person with last login date

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
infomamun
Forum Contributor
Posts: 102
Joined: Mon Dec 28, 2009 7:48 pm

how to show each person with last login date

Post by infomamun »

Hi there,
I have two tables in my database:

Table1: Purchased Item
=====================
Item | Qnt | Rate |Total
=====================
Meat | 1 | 20 | 20
-----------------------------
Fish | 0.5 | 30 | 15
-----------------------------
Chicken|1 | 25 | 25
-----------------------------
Meat | 2 | 20 | 40
======================

Table2: Trading Price
===============================
Item | --------Datetime------- | Price
===============================
Meat | 2013-02-20 10:00:00 | 20
-------------------------------------------
Meat | 2013-02-20 09:00:00 | 18
-------------------------------------------
Meat | 2013-02-19 08:00:00 | 21
-------------------------------------------
Fish | 2013-02-19 09:00:00 | 17
-----------------------------------------
Fish | 2013-02-19 08:00:00 | 15
-----------------------------------------
Chicken|2013-02-20 09:00:00|26
-------------------------------------------
Chicken|2013-02-20 08:00:00|25
===============================

The Table1 lists purchased Item and Table2 updates in every hour with the current price of each Item. So from Table2, it is clearly seen that Meat was last traded on 20-02-2013 at 10 AM Whereas the Fish was not traded on the same day, it was traded on 19-02-2013 at 9AM and the Chicken was traded on 20-02-2013 at 9AM. What I want to do, list all items from table 1 and join the last trade price of respective items from table 2 which will like this:

Output:
==================================
Item | Qty | Total Buy | Last Trade Price
==================================
Meat | 3 |60 | 20
---------------------------
Fish | 0.5 |15 | 17
---------------------------
Chicken|1|25|26
==================================


What type of join and what clause should applied here to get the desirable output?
I tried with this query:
SELECT p.Item, p.Qnt l.price FROM Table1 as p INNER JOIN Table2 as l ON p.Item=l.Item WHERE l.Datetime=(SELECT max(Datetime) FROM Table2);
But by this query I am not finding the desired result as max Datetime is not same for each item, that's why item is missing from output.

So what join type and what condition should be applied in WHERE Clause to get the above Output?
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: how to show each person with last login date

Post by Christopher »

I think you want to try GROUP BY Table2 in conjunction with MAX(Datetime).
(#10850)
infomamun
Forum Contributor
Posts: 102
Joined: Mon Dec 28, 2009 7:48 pm

Re: how to show each person with last login date

Post by infomamun »

Christopher wrote:I think you want to try GROUP BY Table2 in conjunction with MAX(Datetime).
Could not understand what you said (Group by table??? :roll: )
I have tried this after your post:
SELECT p.Item, p.Qnt l.price FROM Table1 as p INNER JOIN Table2 as l ON p.Item=l.Item WHERE l.Datetime=(SELECT max(Datetime) FROM Table2 GROUP BY Item);
But it is returing this error:
Subquery returns more than 1 row

Would you explain little bit with code please?
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: how to show each person with last login date

Post by Christopher »

I think just something like:

SELECT p.Item, p.Qnt l.price,MAX(l.Datetime) FROM Table1 as p INNER JOIN Table2 as l ON p.Item=l.Item GROUP BY l.Datetime;
(#10850)
infomamun
Forum Contributor
Posts: 102
Joined: Mon Dec 28, 2009 7:48 pm

Re: how to show each person with last login date

Post by infomamun »

This is not outputting the result correctly.
It is repeating and including all dates and respective price from Table2 in output.
I think, it should be done with Left or Right join, but I cannot find out how to bring the desired result.
Please look once again to reveal out a way.
Thanks in Advance.
Post Reply