SQL joins and nulls

From: Have a nice day! <no email>
Date: Mon, 19 Apr 1999 21:22:02 GMT
Message-ID: <371c9e71.1932659@news.demon.co.uk>

This a probably a really easy question but......

I have the following query

SELECT REVENUE_ACCOUNTS.RAC_TCY_ACCNO,
DEBIT_BREAKDOWNS.DBR_REBATEABLE, REVENUE_ACCOUNTS.RAC_VAT_IND, decode(dbr_ety_value,'',RER_ELEM_RATE,dbr_ety_value) as Amount, decode(atty_name,'',ETY_NAME, ATTY_NAME) as Description FROM revenue_accounts, debit_breakdowns, rent_elemrates, element_types, attribute_types
where REVENUE_ACCOUNTS.RAC_ACCNO = DEBIT_BREAKDOWNS.DBR_RAC_ACCNO and DEBIT_BREAKDOWNS.DBR_ATTY_CODE = RENT_ELEMRATES.RER_ATTY_CODE(+) and DEBIT_BREAKDOWNS.DBR_ETY_CODE = RENT_ELEMRATES.RER_ETY_CODE(+)

and it works OK (it could probable be better but I'm normally a lowly Access programmer). The problem I've got is that it ignores nulls in the joins.

For example, if the fields are filled like this, everything is OK

REVENUE_ACCOUNTS.RAC_ACCNO		12345	 
DEBIT_BREAKDOWNS.DBR_RAC_ACCNO	12345
DEBIT_BREAKDOWNS.DBR_ATTY_CODE	CodeA
RENT_ELEMRATES.RER_ATTY_CODE		CodeA
DEBIT_BREAKDOWNS.DBR_ETY_CODE	CodeB
RENT_ELEMRATES.RER_ETY_CODE		CodeB

However, if they are filled like this, the query will not return any data (but I want them to)

REVENUE_ACCOUNTS.RAC_ACCNO		12345	 
DEBIT_BREAKDOWNS.DBR_RAC_ACCNO	12345
DEBIT_BREAKDOWNS.DBR_ATTY_CODE	CodeA
RENT_ELEMRATES.RER_ATTY_CODE		
DEBIT_BREAKDOWNS.DBR_ETY_CODE	CodeB

RENT_ELEMRATES.RER_ETY_CODE How do I get this to work?

Thanks in advance

Dom
dominic.edwards_at_bigfoot.com Received on Mon Apr 19 1999 - 16:22:02 CDT