How to introduce blank (or Null) value where value does not exist in a table

[taryn]

I have two tables,

CR_RefMaster and

CR_Ref

Addresses. I am trying to join information from both (using the RefCode column) and return a blank or Null value for the

CR_Ref

Addresses columns

where the RefCode does not exist in

CR_Ref

Addresses

.



I have created the following query, but LEFT JOIN does not seem to be working. Where

the RefCode does not exist in CR_Ref

Addresses, what is happening is that the entire row for that RefCode goes missing:

SELECT DISTINCT CR_RefMaster.Name, CR_RefMaster.RefCode,

CR_RefAddresses.

Country,

CR_RefAddresses.

AddrType
FROM CR_RefMaster LEFT JOIN CR_RefAddresses ON CR_RefMaster.RefCode=CR_RefAddresses.RefCode
WHERE

CR_RefAddresses.

AddrType In ('RO','RA') AND

CR_RefAddresses.

CardActive<>0
AND

CR_RefMaster.

Status='0' AND CR_RefMaster.CS=0 AND CR_RefMaster.RefType<>'O'
ORDER BY

CR_RefMaster.

Name

I hope someone can help.

Kind regards.

Taryn

[rpeare]

if your criteria are referencing fields on the CR_RefAddresses table that'll cut out your null values. If you want your null values to show you'd have to test for those as well

i.e.

where (CR_Addresses.AddrType in ('RO','RA') OR CR_Addresses.AddrType is null)
AND ...

you'd have to do this in your where statement for any field from the CR_Addresses table.

[taryn]

Thanks for your prompt reply. The above has worked.

However, I have noticed a second problem now, which is that where there IS a

CR_RefAddresses.

Country, but it is not of

CR_Addresses.AddrType

'RO' or 'RA', the result is also being cut. In this case, I would still like a null value to show for

CR_RefAddresses.Country

.

Is this possible?

Kind regards.

Taryn

[rpeare]

as I said, for every field in CR_Addresses you have to test for what you're looking for AND for a null value for it to show, for instance the 'cardactive' field in your original post would have to be tested for <>0 and null, country would have to be tested for the value you're searching for and null, etc, etc, etc.

[taryn]

Hi

I'm sorry if I didn't express my second problem clearly. I changed my initial SELECT query as you suggested for all fields which use CR_Addresses and this solved by initial problem, so thanks for that.

What I then discovered is that I seem to have a second problem, independent of the first. I realised that results where CR_Addresses.AddrType equal anything other than 'RO' or 'RA' (as per the criteria) are also being cut out, when what I need is a Null value result. Is there any way to achieve this?

Thanks.

Taryn

[rpeare]

So if the value in AddrType is anything but RO or RA you want to display a null value?

If so I am not sure you can put a criteria on it but you can get the effect in a calculated field:

iif(AddrType = 'RO' or AddrType = 'RA', AddrType, null)

[ArviLaanemets]

Maybe

SELECT DISTINCT 
rm.Name, 
rm.RefCode,
Nz(ra.Country,Null) AS Country,
Nz(ra.AddrType,Null) AS AddrType
FROM CR_RefMaster rm LEFT JOIN (SELECT ra0.RefCode, ra0.Country, ra0.AddrType FROM CR_RefAddresses ra0 WHERE ra0.AddrType In ('RO','RA') AND ra0.CardActive <> 0) ra ON rm.RefCode=ra.RefCode
WHERErm.Status='0' AND rm.CS=0 AND rm.RefType<>'O' 
ORDER BY rm.Name

[taryn]

Thank you for your replies. I shall try your suggestions out when I'm back in the office.

Taryn ☺

[taryn]

Maybe

SELECT DISTINCT 
rm.Name, 
rm.RefCode,
Nz(ra.Country,Null) AS Country,
Nz(ra.AddrType,Null) AS AddrType
FROM CR_RefMaster rm LEFT JOIN (SELECT ra0.RefCode, ra0.Country, ra0.AddrType FROM CR_RefAddresses ra0 WHERE ra0.AddrType In ('RO','RA') AND ra0.CardActive <> 0) ra ON rm.RefCode=ra.RefCode
WHERErm.Status='0' AND rm.CS=0 AND rm.RefType<>'O' 
ORDER BY rm.Name

Thank you, this has worked for me.

Just out of curiosity, why didn't the LEFT JOIN / LEFT OUTER JOIN work on its own? I was under the impression that "

The

LEFT JOIN

command returns all rows from the left table, and the matching rows from the right table. The result is NULL from the right side, if there is no match."

Thank you for your help.

Kind regards.

Taryn

[ArviLaanemets]

You had the condition for excluding records with address type not in list for main part of query. Join was done before this condition was applied!

e.g. in SQL Server databas you could use the syntax

...
FROM CR_RefMaster rm LEFT JOIN CR_RefAddresses ra  ON rm.RefCode=ra.RefCode AND rm.AddrType In ('RO','RA') 
...

, but Access does'nt support this.

[taryn]

Thanks for explaining