Looking for left outer join help on this sql.

SELECT
ALL D.BPASDT, D.BPASTS, A.ABABTX, A.ABACCD,
A.ABAECD,
SUBSTR(C.ACAKCD,1,4)||'-'||SUBSTR(C.ACALCD,1,2)||'-'||
SUBSTR(
C.ACAMCD,1,4) AS PARCEL, A.ABAUCD, A.ABAJCD, B.ANBKCD,
B.ANALTX,
D.BPATYP, E.BPATDS, D.BPPYER,
D.BPPCNB
FROM HTEDTA/LMABREP
A,
HTEDTA/LMANREP
B,
HTEDTA/LMACREP
C,
HTEDTA/BP200AP
D,
HTEDTA/BP960AP
E
WHERE A.ABBKCD =
B.ANBKCD
AND A.ABAJCD =
C.ACAJCD
AND A.ABAUCD =
D.BPLCID
AND D.BPATYP =
E.BPATCD
AND( D.BPASTS =
'CO'
AND D.BPPYER = 7
AND D.BPASDT BETWEEN 070801 AND 070831)
ORDER BY E.BPATDS ASC, D.BPASDT ASC, A.ABABTX ASC

On Aug 16, 10:00 am, jacko <jkings...@cityofnorthport.com> wrote:[color=blue]
> SELECT
> ALL D.BPASDT, D.BPASTS, A.ABABTX, A.ABACCD,
> A.ABAECD,
> SUBSTR(C.ACAKCD,1,4)||'-'||SUBSTR(C.ACALCD,1,2)||'-'||
> SUBSTR(
> C.ACAMCD,1,4) AS PARCEL, A.ABAUCD, A.ABAJCD, B.ANBKCD,
> B.ANALTX,
> D.BPATYP, E.BPATDS, D.BPPYER,
> D.BPPCNB
> FROM HTEDTA/LMABREP
> A,
> HTEDTA/LMANREP
> B,
> HTEDTA/LMACREP
> C,
> HTEDTA/BP200AP
> D,
> HTEDTA/BP960AP
> E
> WHERE A.ABBKCD =
> B.ANBKCD
> AND A.ABAJCD =
> C.ACAJCD
> AND A.ABAUCD =
> D.BPLCID
> AND D.BPATYP =
> E.BPATCD
> AND( D.BPASTS =
> 'CO'
> AND D.BPPYER = 7
> AND D.BPASDT BETWEEN 070801 AND 070831)
> ORDER BY E.BPATDS ASC, D.BPASDT ASC, A.ABABTX ASC[/color]

You don't give us much info, but if you just want to change the inner
join to a left outer join on all tables, try this

SELECT
D.BPASDT, D.BPASTS, A.ABABTX, A.ABACCD,
A.ABAECD, SUBSTR(C.ACAKCD,1,4)||'-'||SUBSTR(C.ACALCD,1,2)||'-'||
SUBSTR(C.ACAMCD,1,4) AS PARCEL, A.ABAUCD, A.ABAJCD, B.ANBKCD,
B.ANALTX, D.BPATYP, E.BPATDS, D.BPPYER,
D.BPPCNB

FROM HTEDTA/LMABREP A

Left Outer Join HTEDTA/LMANREP B
on A.ABBKCD = B.ANBKCD

Left Outer Join HTEDTA/LMACREP C
on A.ABAJCD = C.ACAJCD

Left outer Join HTEDTA/BP200AP D
on A.ABAUCD = D.BPLCID

Left outer Join HTEDTA/BP960AP E
on D.BPATYP = E.BPATCD

WHERE
D.BPASTS = 'CO'
AND D.BPPYER = 7
AND D.BPASDT BETWEEN 070801 AND 070831

ORDER BY E.BPATDS ASC, D.BPASDT ASC, A.ABABTX ASC

"Graybeard" <graybeard@fastmail.co.uk> skrev i en meddelelse
news:1187273396.347083.284180@r29g2000hsg.googlegroups.com...[color=blue]
> On Aug 16, 10:00 am, jacko <jkings...@cityofnorthport.com> wrote:[color=green]
>> SELECT
>> ALL D.BPASDT, D.BPASTS, A.ABABTX, A.ABACCD,
>> A.ABAECD,
>> SUBSTR(C.ACAKCD,1,4)||'-'||SUBSTR(C.ACALCD,1,2)||'-'||
>> SUBSTR(
>> C.ACAMCD,1,4) AS PARCEL, A.ABAUCD, A.ABAJCD, B.ANBKCD,
>> B.ANALTX,
>> D.BPATYP, E.BPATDS, D.BPPYER,
>> D.BPPCNB
>> FROM HTEDTA/LMABREP
>> A,
>> HTEDTA/LMANREP
>> B,
>> HTEDTA/LMACREP
>> C,
>> HTEDTA/BP200AP
>> D,
>> HTEDTA/BP960AP
>> E
>> WHERE A.ABBKCD =
>> B.ANBKCD
>> AND A.ABAJCD =
>> C.ACAJCD
>> AND A.ABAUCD =
>> D.BPLCID
>> AND D.BPATYP =
>> E.BPATCD
>> AND( D.BPASTS =
>> 'CO'
>> AND D.BPPYER = 7
>> AND D.BPASDT BETWEEN 070801 AND 070831)
>> ORDER BY E.BPATDS ASC, D.BPASDT ASC, A.ABABTX ASC[/color]
>
> You don't give us much info, but if you just want to change the inner
> join to a left outer join on all tables, try this
>
> SELECT
> D.BPASDT, D.BPASTS, A.ABABTX, A.ABACCD,
> A.ABAECD, SUBSTR(C.ACAKCD,1,4)||'-'||SUBSTR(C.ACALCD,1,2)||'-'||
> SUBSTR(C.ACAMCD,1,4) AS PARCEL, A.ABAUCD, A.ABAJCD, B.ANBKCD,
> B.ANALTX, D.BPATYP, E.BPATDS, D.BPPYER,
> D.BPPCNB
>
> FROM HTEDTA/LMABREP A
>
> Left Outer Join HTEDTA/LMANREP B
> on A.ABBKCD = B.ANBKCD
>
> Left Outer Join HTEDTA/LMACREP C
> on A.ABAJCD = C.ACAJCD
>
> Left outer Join HTEDTA/BP200AP D
> on A.ABAUCD = D.BPLCID
>
> Left outer Join HTEDTA/BP960AP E
> on D.BPATYP = E.BPATCD
>
> WHERE
> D.BPASTS = 'CO'
> AND D.BPPYER = 7
> AND D.BPASDT BETWEEN 070801 AND 070831
>
> ORDER BY E.BPATDS ASC, D.BPASDT ASC, A.ABABTX ASC
>[/color]

Hmmm... Not much point in specifying outer joins unless you also allow null
values in your WHERE clause:


WHERE (D.BPASTS IS NULL OR D.BPASTS = 'CO' )
AND (D.BPPYER IS NULL OR D.BPPYER = 7 )
AND (D.BPASDT IS NULL OR D.BPASDT BETWEEN 070801 AND 070831)