Complex SQL Query Design Help

[ulicki]

Hi,

I need to add another table to my SQL Query below.
The current tables in my Query are
GRANTZ, OPTIONEE, EXERCISE, AND DISPOSE.
The relationships are as follows
One GRANTZ has one and only one OPTIONEE
One GRANTZ has zero to many EXERCISE
One EXERCISE has zero to many DISPOSE.

Primary Keys: GRANTZ(grant_num), OPTIONEE(opt_num), EXERCISE(exer_num), DISPOSE(disp_num).
Foreign Keys: opt_num is a FK in the GRANTZ table, grant_num is a FK in the EXERCISE table, exer_num is a FK in the DISPOSE table.

I want to add the PLAN table.
One GRANTZ has one only one PLAN. PK is plan_num and plan_num is a FK in the GRANTZ table.

I would also like to add another join from the EXERCISE table to the DISPOSE table, exercise.EXER_DATE = dispose.DISPOSE_DATE, in addtion to one in the query below.

A little history: I tried to put all the joins in the where clause but SQL SERVER does not like it. So I made the below query which works fine, however I have no idea how the syntax works as I copied it from another query replacing in my tables.

SELECT
grantz.GRANT_NUM, grantz.OPT_NUM, grantz.OPTS_GRNTD, grantz.PLAN_TYPE, grantz.SAR_FLAG, grantz.VEST_NUM, grantz.TERM_VEST, grantz.EXBL_FLAG, grantz.VEST_TYPE, grantz.VEST_DT, grantz.TERM_DT, grantz.GRANT_DT, grantz.EXPR_DT, grantz.RPCH_FLAG,
optionee.OPT_NUM, optionee.NAME_FIRST, optionee.NAME_MI, optionee.OPT_ID, optionee.NAME_LAST, optionee.NICKNAME, optionee.USER_CD3,
exercise.EXER_NUM, exercise.EXER_ID, exercise.OPTS_EXER, exercise.EXER_TYPE, exercise.EXER_DT,
dispose.SHRS_SOLD
FROM
{ oj ((JPMC37014.dbo.grantz grantz LEFT OUTER JOIN JPMC37014.dbo.exercise exercise ON grantz.GRANT_NUM = exercise.GRANT_NUM) LEFT OUTER JOIN JPMC37014.dbo.optionee optionee ON grantz.OPT_NUM = optionee.OPT_NUM) LEFT OUTER JOIN JPMC37014.dbo.dispose dispose ON exercise.EXER_NUM = dispose.EXER_NUM}
ORDER BY
optionee.OPT_ID ASC, optionee.OPT_NUM ASC, grantz.GRANT_NUM ASC, exercise.EXER_NUM ASC

 

[hirick]

The following will work if there is always a record in plan matching the record in grantz. If not, change the word INNER to LEFT OUTER.
Change your FROM clause as follows:

JPMC37014.dbo.grantz grantz
LEFT OUTER JOIN JPMC37014.dbo.exercise exercise ON
grantz.GRANT_NUM = exercise.GRANT_NUM
LEFT OUTER JOIN JPMC37014.dbo.optionee optionee ON
grantz.OPT_NUM = optionee.OPT_NUM
LEFT OUTER JOIN JPMC37014.dbo.dispose dispose ON
exercise.EXER_NUM = dispose.EXER_NUM AND
exercise.EXER_DATE = dispose.DISPOSE_DATE
INNER JOIN JPMC37014.dbo.plan plan ON
grantz.plan_num = plan.plan_num

 

[ulicki]

Thanks hirick,

This code is much easier to follow than what I was trying to do.