pmarkov
asked on
Query
Hello
My DataBase is Paradox.
And I have 3 tables
table 1:
ID BeignDate Price
1 1-1-2001 1.21
1 1-2-2001 5.21
1 2-2-2002 10.22
2 5-5-2002 22.22
3 2-2-2002 1.11
table 2:
ID BeginDate Correction
1 1-3-2001 -.50
1 1-4-2001 +.10
2 1-1-2002 -.02
5 6-6-2002 10.01
Table 3
ID Name
1 A
2 B
3 C
4 D
5 E
I want to select The LAST Price for each ID
Price := Price + Correction
The Result Table:
ID Last Price
1 10.32
2 22.20
3 1.11
5 10.01
Can anybody help me with SQL Statement?
Thanks.
My DataBase is Paradox.
And I have 3 tables
table 1:
ID BeignDate Price
1 1-1-2001 1.21
1 1-2-2001 5.21
1 2-2-2002 10.22
2 5-5-2002 22.22
3 2-2-2002 1.11
table 2:
ID BeginDate Correction
1 1-3-2001 -.50
1 1-4-2001 +.10
2 1-1-2002 -.02
5 6-6-2002 10.01
Table 3
ID Name
1 A
2 B
3 C
4 D
5 E
I want to select The LAST Price for each ID
Price := Price + Correction
The Result Table:
ID Last Price
1 10.32
2 22.20
3 1.11
5 10.01
Can anybody help me with SQL Statement?
Thanks.
I suppose the Fields are not NULL although.
Sincerely,
Nestorua.
Sincerely,
Nestorua.
ASKER
Thanks! :)))
This is good but there is one problem.
Look at row 4 in Result table where ID = 5!
ID = 5 exist in table 2 but NOT exist in table 1 :)
Thaks againg.
This is good but there is one problem.
Look at row 4 in Result table where ID = 5!
ID = 5 exist in table 2 but NOT exist in table 1 :)
Thaks againg.
ASKER
... and what about Table 3? Can you put the NAME of ID in the result table?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This is good but there is one problem.
Look at row 4 in Result table where ID = 5!
ID = 5 exist in table 2 but NOT exist in table 1 :)
Thaks againg.
Look at row 4 in Result table where ID = 5!
ID = 5 exist in table 2 but NOT exist in table 1 :)
Thaks againg.
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
Accept answer from nestorua
Please leave any comments here within the next seven days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
Paul (pnh73)
EE Cleanup Volunteer
I will leave a recommendation in the Cleanup topic area that this question is:
Accept answer from nestorua
Please leave any comments here within the next seven days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
Paul (pnh73)
EE Cleanup Volunteer
Try this:
SELECT A.ID, A.PRICE+B.CORRECTION AS LASTPRICE
FROM TABLE1 A
JOIN TABLE2 B ON B.ID=A.ID
WHERE (A.BEGINDATE=
(SELECT MAX(BEGINDATE) FROM TABLE1
WHERE ID=A.ID))
AND
(B.BEGINDATE=
(SELECT MAX(BEGINDATE) FROM TABLE2
WHERE ID=B.ID))
If everything will be good - fine, if not - inform me.
Sincerely,
Nestorua.