• Status: Solved
• Priority: Medium
• Security: Public
• Views: 161

# Query

Hello

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.
0
pmarkov
• 3
• 3
1 Solution

Commented:
HI,
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.
0

Commented:
I suppose the Fields are not NULL although.
Sincerely,
Nestorua.
0

Author Commented:
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.
0

Author Commented:
... and what about  Table 3? Can you put the NAME of ID in the result table?
0

Commented:
HI,
From your example Table3 doesn't effect onthe result.

Sincerely,
Nestorua.
0

Author Commented:
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.
0

Commented:
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:

Paul (pnh73)
EE Cleanup Volunteer
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.