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

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.
0
pmarkov
Asked:
pmarkov
  • 3
  • 3
1 Solution
 
nestoruaCommented:
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
 
nestoruaCommented:
I suppose the Fields are not NULL although.
Sincerely,
Nestorua.
0
 
pmarkovAuthor 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
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
pmarkovAuthor Commented:
... and what about  Table 3? Can you put the NAME of ID in the result table?
0
 
nestoruaCommented:
HI,
From your example Table3 doesn't effect onthe result.

Sincerely,
Nestorua.
0
 
pmarkovAuthor 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
 
pnh73Commented:
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
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now