|
Home > Archive > ASE Database forum > April 2005 > Outer Join
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
| Charles Bonsu 2005-04-26, 9:24 am |
| I have the following query with an outer join on a view
"a3". The view is also used in the last two join statements
to filter information. I do not believe this is proper.
Could somebody show me how to rewrite
SELECT
isNull(SUM(b.budg_splt_tot_dol), 0)
FROM
cd.prop p,
cd.prop_atr a,
cd.prop p2,
cd.prop_atr a2,
cd.prop_atr a3,
cd.budg_splt b
WHERE
p.prop_id = '487'
AND p.prop_id = a.prop_id
AND a.prop_atr_code = p2.prop_id
AND p2.prop_id = a2.prop_atr_code
AND a2.prop_id *= a3.prop_id -- view used in outer join
here
AND a.prop_atr_type_code = 'PAD'
AND a2.prop_id = b.prop_id
AND a2.prop_atr_type_code = 'PAD'
AND a3.prop_atr_type_code = 'ESU' -- view used in
filter here
AND a3.prop_atr_code NOT IN ('A','R','N','Q','C'
) --
here again
| |
| ViBart 2005-04-26, 11:25 am |
| Welcome,
SELECT
isNull(SUM(b.budg_splt_tot_dol), 0)
FROM
cd.prop p
INNER JOIN cd.prop_atr a,
ON a.prop_id = p.prop_id
INNER JOIN cd.prop p2
ON p2.prop_id = a.prop_atr_code
INNER JOIN cd.prop_atr a2
ON a2.prop_atr_code = p2.prop_id
LEFT OUTER JOIN cd.prop_atr a3
ON a3.prop_id = a2.prop_id
AND a3.prop_atr_type_code = 'ESU'
AND a3.prop_atr_code NOT IN ('A','R','N','Q','C'
)
INNER JOIN cd.budg_splt b
ON b.prop_id = a2.prop_id
WHERE p.prop_id = '487'
AND a.prop_atr_type_code = 'PAD'
AND a2.prop_atr_type_code = 'PAD'
HTH
Best Regards
ViBart
>I have the following query with an outer join on a view
> "a3". The view is also used in the last two join statements
> to filter information. I do not believe this is proper.
> Could somebody show me how to rewrite
>
> SELECT
> isNull(SUM(b.budg_splt_tot_dol), 0)
> FROM
> cd.prop p,
> cd.prop_atr a,
> cd.prop p2,
> cd.prop_atr a2,
> cd.prop_atr a3,
> cd.budg_splt b
> WHERE
> p.prop_id = '487'
> AND p.prop_id = a.prop_id
> AND a.prop_atr_code = p2.prop_id
> AND p2.prop_id = a2.prop_atr_code
> AND a2.prop_id *= a3.prop_id -- view used in outer join
> here
> AND a.prop_atr_type_code = 'PAD'
> AND a2.prop_id = b.prop_id
> AND a2.prop_atr_type_code = 'PAD'
> AND a3.prop_atr_type_code = 'ESU' -- view used in
> filter here
> AND a3.prop_atr_code NOT IN ('A','R','N','Q','C'
) --
> here again
| |
| Charles Bonsu 2005-04-26, 1:24 pm |
| I do not usually use the inner join clause but what is the
difference between you code and mine or that is the correct
way to write it?
Charles
> Welcome,
>
> SELECT
> isNull(SUM(b.budg_splt_tot_dol), 0)
> FROM
> cd.prop p
> INNER JOIN cd.prop_atr a,
> ON a.prop_id = p.prop_id
> INNER JOIN cd.prop p2
> ON p2.prop_id = a.prop_atr_code
> INNER JOIN cd.prop_atr a2
> ON a2.prop_atr_code = p2.prop_id
> LEFT OUTER JOIN cd.prop_atr a3
> ON a3.prop_id = a2.prop_id
> AND a3.prop_atr_type_code = 'ESU'
> AND a3.prop_atr_code NOT IN ('A','R','N','Q','C'
)
> INNER JOIN cd.budg_splt b
> ON b.prop_id = a2.prop_id
> WHERE p.prop_id = '487'
> AND a.prop_atr_type_code = 'PAD'
> AND a2.prop_atr_type_code = 'PAD'
>
> HTH
>
> Best Regards
> ViBart
>
>
>
| |
| Charles Bonsu 2005-04-26, 1:24 pm |
| Vibart
I do not use the inner join clause so what is the difference
between your code and mine or is that the correct and
logical way to frame my code?
Charles
> Welcome,
>
> SELECT
> isNull(SUM(b.budg_splt_tot_dol), 0)
> FROM
> cd.prop p
> INNER JOIN cd.prop_atr a,
> ON a.prop_id = p.prop_id
> INNER JOIN cd.prop p2
> ON p2.prop_id = a.prop_atr_code
> INNER JOIN cd.prop_atr a2
> ON a2.prop_atr_code = p2.prop_id
> LEFT OUTER JOIN cd.prop_atr a3
> ON a3.prop_id = a2.prop_id
> AND a3.prop_atr_type_code = 'ESU'
> AND a3.prop_atr_code NOT IN ('A','R','N','Q','C'
)
> INNER JOIN cd.budg_splt b
> ON b.prop_id = a2.prop_id
> WHERE p.prop_id = '487'
> AND a.prop_atr_type_code = 'PAD'
> AND a2.prop_atr_type_code = 'PAD'
>
> HTH
>
> Best Regards
> ViBart
>
>
>
|
|
|
|
|