Self Join clarification for a particular query.

[webdevGeorge]

Hello all,

I'd like some help with my self join query. This is what it looks like so far:

select distinct

prcaccount_1.CODPRC,
aggregates_1.MNEAGR AS AGRPRG,
aggregates_2.MNEAGR AS AGRCHPFRA,
aggregates_1.INDFAM AS INDFAM

from

TICAPRCBDR prcaccount_1, TICALIEBDR linktable_1, TICAAGRBDR aggregates_1
TICAPRCBDR prcaccount_2, TICALIEBDR linktable_2, TICAAGRBDR aggregates_2

where

prcaccount_1.MNEPRC LIKE 'PRC%'

and linktable_1.CODNOD = prcaccount_1.codprc
and linktable_1.typnodprt = 'AGRPRG'
and aggregates_1.codagr = linktable_1.codnodprt

and linktable_2.CODNOD = prcaccount_2.codprc
and linktable_2.typnodprt = 'AGRCHPFRA'
and aggregates_2.codagr = linktable_2.codnodprt

and prcaccount_1.codprc = prcaccount_2.codprc

order by prcaccount_1.CODPRC


And this is the two records it is returning me:


CODPRC AGRPRG AGRCHPFRA INDFAM
5000001 AGR99312 AGR97212 COR
5000005 AGR99312 AGR97213 COR

When what I really want is this:

CODPRC | AGRCHPFRA | AGRPRG | INDFAM
5000000 | null | AGR99111 | null
5000001 | AGR97212 | AGR99312 | COR
5000002 | null | AGR9912 | null
5000005 | AGR97213 | AGR99312 | COR
5000006 | null | AGR99121 | null
5000007 | null | AGR99122 | null
5000022 | null | AGR99121 | null


As you can see, I'm only retrieving records that have an AGRCHPFRA AND an AGRPRG when I really just want everything regardless if there is data or not for those two columns

I think I have to use a Left Join to accomplish this but I'm not that SQL savvy. Any hints??

I'm sorry for the lack of clarity!!!

Thanks!