Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# Query

Posted on 2002-05-11
Medium Priority
157 Views
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
Question by:pmarkov
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 3
• 3

LVL 4

Expert Comment

ID: 7003097
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

LVL 4

Expert Comment

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

Author Comment

ID: 7005263
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 Comment

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

LVL 4

Accepted Solution

nestorua earned 400 total points
ID: 7006471
HI,
From your example Table3 doesn't effect onthe result.

Sincerely,
Nestorua.
0

Author Comment

ID: 7007741
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

LVL 1

Expert Comment

ID: 9005623
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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
###### Suggested Courses
Course of the Month8 days, 15 hours left to enroll