linking tables with null value 1

[Lobke]

I need to link 2 tables based on 3 fields
But the 3rd field can contain null values, the problem is that when I link them I don't get results for the rows with null values
a, b, c -> a, b, c = works fine
a, b, null -> a, b, null = no results

is there a way to solve this? Or a way to change the null to 0?

 

[lbass]

Use a left join FROM the table with all records TO the table that contains nulls. Be sure not to use any selection criteria on the table containing nulls.

-LB

 

[Lobke]

That doesn't work because both tables have the null in the 3rd column.
The fields contain an address: street, number, subnumber
the subnumber is for apartments only so it's null most of the time.
I need to link the null of table1 with the null in table2
or change the null to 0

 

[Turkbear]

Hi,
A NULL can never link to another NULL ( or anything else), since NULL is not = to anything, including NULL.

You will probably need to change the NULLs into 0s in the database since the data is returned before any report-based conversion can happen and it is the returned data that would not have the NULL-containing records.







To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

Instead of linking the tables, you could potentially use a command as your datasource with a union all to "merge" records from each table into the same field, as in:

select table1.street, table1.number, table1.subnumber
from table1
union all
select table2.street, table2.number, table2.subnumber
from table2

Then you could group on these fields to get the desired results.

-LB

 

[TeyBrady]

If you are using Oracle you could use nvl.

nvl(c,'X') Returns an X if c is null otherwise it returns c