A Where statement containing information from two rows

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Dear All,
I want to perform a query on my database where I want to select the returns
of all shares for a certain period that have a rating at a specific month. So
far I have the following query:

SELECT kr_oper.FUNDNAME, kr_star_l.month, kr_star_l.rating,
kr_star_l.category, kr_star_l.catcode, kr_ret_l.RETURN
FROM (kr_oper INNER JOIN kr_ret_l ON kr_oper.SECID = kr_ret_l.SECID) INNER
JOIN kr_star_l ON (kr_ret_l.MONTH = kr_star_l.month) AND (kr_ret_l.SECID =
kr_star_l.secid)

The variable describing the months is "kr_star_l.month" and the variable
describing the rating is "kr_star_l.rating"
kr_star_l.month should be > 199502
kr_star_l.rating should be > 0

What WHERE statement should I use to select all monthly data (from 199503 to
200509) on those shares that have a rating at 199503 (March 1995)

Thank you for your help!
 
Dear Ralph:

Below is your posted query, edited to help me read it better. I post this
for my future reference:

SELECT O.FUNDNAME, S1.month, Sl.rating,
Sl.category, S.catcode, Rl.RETURN
FROM (kr_oper O
INNER JOIN kr_ret_l R1
ON O.SECID = Rl.SECID)
INNER JOIN kr_star_l S1
ON Rl.MONTH = Sl.month AND Rl.SECID = Sl.secid

I suggest adding:

WHERE EXISTS (SELECT * FROM kr_star_1 S2
WHERE S2.MONTH = 199503 AND S2.secid = R1.SECID)
AND R1.MONTH BETWEEN 199503 AND 200509

The thing in the parentheses is a subquery, one that is correlated on MONTH
and SECID. It checks for the existence of one or more rows that match the
criteria you specify.

The query limiting the months in the kr_star_1 table is fairly straight
forward.

I'm not sure if I have exactly understood your specifications and existing
query work. If you put together the query sections I have made above,
please test this and let me know.

In order to study and understand what I have done, you need to look at the
topics for aliasing and subqueries.

Tom Ellison
 
Dear Tom,

Thank you for your reply.
Your added query prompts me for a secid and a month value. In total there
are 25202 funds of which only 2602 have a rating on 199503. I have checked
this by adding WHERE (((kr_star_l.month)=199503) AND ((kr_star_l.rating)>0))
to the query.

If you could help me get all months >199502 for those 2602 funds that have a
rating on 199503 without access prompting me for parameters, that would help
me out a lot.
 
Dear Ralph:

The fact that it is prompting you for secid and month indicates that I have
used these column names in reference to a table that does not contain those
columns. This is just a problem with my understanding of your table design.

First, please run just this much:

SELECT O.FUNDNAME, S1.month, Sl.rating,
Sl.category, S.catcode, Rl.RETURN
FROM (kr_oper O
INNER JOIN kr_ret_l R1
ON O.SECID = Rl.SECID)
INNER JOIN kr_star_l S1
ON Rl.MONTH = Sl.month AND Rl.SECID = Sl.secid

Does this have the problem? If not, please try this:

SELECT O.FUNDNAME, S1.month, Sl.rating,
Sl.category, S.catcode, Rl.RETURN
FROM (kr_oper O
INNER JOIN kr_ret_l R1
ON O.SECID = Rl.SECID)
INNER JOIN kr_star_l S1
ON Rl.MONTH = Sl.month AND Rl.SECID = Sl.secid
WHERE EXISTS (SELECT * FROM kr_star_1 S2
WHERE S2.MONTH = 199503 AND S2.secid = R1.SECID)

Since it is difficult for me to see just how your tables are constituted, I
need you to be moderately vigilant to find any references I have mistaken.
If you were to study the techniques I'm using, specifically "subqueries" and
"aliasing," you would be able to correct this yourself. Since it is my
intention to help you learn this, that would be optimal.

Tom Ellison
 
Back
Top