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?
how to show each person with last login date
Moderator: General Moderators
- 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
I think you want to try GROUP BY Table2 in conjunction with MAX(Datetime).
(#10850)
Re: how to show each person with last login date
Could not understand what you said (Group by table???Christopher wrote:I think you want to try GROUP BY Table2 in conjunction with MAX(Datetime).
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?
- 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
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;
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)
Re: how to show each person with last login date
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.
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.