How to return all rows From one table?
Posted: Mon Mar 04, 2013 10:50 am
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 | 15
-----------------------------------------
Fish | 2013-02-19 08:00:00 | 17
-----------------------------------------
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 | Last Trade Price
==================================
Meat | 1 | 20 | 20
---------------------------
Meat | 2 | 40 | 20
---------------------------
Fish | 0.5 |15 | 15
---------------------------
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?
Note: Earlier I posted this topic but that was slightly different from this topic and tables.
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 | 15
-----------------------------------------
Fish | 2013-02-19 08:00:00 | 17
-----------------------------------------
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 | Last Trade Price
==================================
Meat | 1 | 20 | 20
---------------------------
Meat | 2 | 40 | 20
---------------------------
Fish | 0.5 |15 | 15
---------------------------
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?
Note: Earlier I posted this topic but that was slightly different from this topic and tables.