469,133 Members | 1,153 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,133 developers. It's quick & easy.

Q on joining tables with nullable fields

Question.
I have a new table that I am adding to a script that I wrote. This
table has 3 fields, the first 2 fields are used in the on statement as
being = other fields in the script.

The first field always has data in it, but the 2nd field is sometimes
null.

So my problem is if both fields have data in them and they both match
to the data in the fields that I am linking them to, then it returns
the 3rd field without a problem. However if the 2nd field is null then
it is returning a null for the 3rd field. I have checked and the field
that I am linking to is null also.

So if I have

select t1.field1, t1.field2, t2.field1, t2.field2, t2.field3
from table1 t1
join table2 t2
on t1.field1=t2.field1 and t1.field2=t2.field2

with 2 records in each table
table1: record1: data, data
record2: data, null
table2: record1: data,data,data
record2: data,null,data

what I get from the script is
record1: data, data,data,data,data
record2: data,null,data,null,null
I would expect
record2: data,null,data,null,data

I hope this makes sense, I didn't want to post the entire actual script
as it is about 150 lines long.

Thanks in advance.

Sep 27 '06 #1
5 1549
mike wrote:
I have a new table that I am adding to a script that I wrote. This
table has 3 fields, the first 2 fields are used in the on statement as
being = other fields in the script.

The first field always has data in it, but the 2nd field is sometimes
null.

So my problem is if both fields have data in them and they both match
to the data in the fields that I am linking them to, then it returns
the 3rd field without a problem. However if the 2nd field is null then
it is returning a null for the 3rd field. I have checked and the field
that I am linking to is null also.

So if I have

select t1.field1, t1.field2, t2.field1, t2.field2, t2.field3
from table1 t1
join table2 t2
on t1.field1=t2.field1 and t1.field2=t2.field2

with 2 records in each table
table1: record1: data, data
record2: data, null
table2: record1: data,data,data
record2: data,null,data

what I get from the script is
record1: data, data,data,data,data
record2: data,null,data,null,null
I would expect
record2: data,null,data,null,data
Please use sample data like 'A', 'B', 'C', etc., instead of "data" which
is much more confusing.

Run some sanity checks on the data:

select * from table1 where field2 is null
select * from table1 where field2 = 'NULL'

select * from table2 where field2 is null
select * from table2 where field2 = 'NULL'

What tool are you using to pull the data? Some clients (e.g. Crystal
Reports) have a "convert nulls to empty values" option.
Sep 27 '06 #2
Yes using select statements like that does return the data where field2
is null.
This is just using query analyzer. This is part of a view that I am
making some changes to.
Also I have tried using the SET ANSI_NULLS ON and SET ANSI_NULLS OFF
and it made no difference.

Ed Murphy wrote:
mike wrote:
I have a new table that I am adding to a script that I wrote. This
table has 3 fields, the first 2 fields are used in the on statement as
being = other fields in the script.

The first field always has data in it, but the 2nd field is sometimes
null.

So my problem is if both fields have data in them and they both match
to the data in the fields that I am linking them to, then it returns
the 3rd field without a problem. However if the 2nd field is null then
it is returning a null for the 3rd field. I have checked and the field
that I am linking to is null also.

So if I have

select t1.field1, t1.field2, t2.field1, t2.field2, t2.field3
from table1 t1
join table2 t2
on t1.field1=t2.field1 and t1.field2=t2.field2

with 2 records in each table
table1: record1: data, data
record2: data, null
table2: record1: data,data,data
record2: data,null,data

what I get from the script is
record1: data, data,data,data,data
record2: data,null,data,null,null
I would expect
record2: data,null,data,null,data

Please use sample data like 'A', 'B', 'C', etc., instead of "data" which
is much more confusing.

Run some sanity checks on the data:

select * from table1 where field2 is null
select * from table1 where field2 = 'NULL'

select * from table2 where field2 is null
select * from table2 where field2 = 'NULL'

What tool are you using to pull the data? Some clients (e.g. Crystal
Reports) have a "convert nulls to empty values" option.
Sep 27 '06 #3
Oh yea, and one thing I forgot is that my join is a Left Outer, if I
make it an inner join the records with the NULL just don't show at all.
So the issue is within the linking.

mike wrote:
Yes using select statements like that does return the data where field2
is null.
This is just using query analyzer. This is part of a view that I am
making some changes to.
Also I have tried using the SET ANSI_NULLS ON and SET ANSI_NULLS OFF
and it made no difference.

Ed Murphy wrote:
mike wrote:
I have a new table that I am adding to a script that I wrote. This
table has 3 fields, the first 2 fields are used in the on statement as
being = other fields in the script.
>
The first field always has data in it, but the 2nd field is sometimes
null.
>
So my problem is if both fields have data in them and they both match
to the data in the fields that I am linking them to, then it returns
the 3rd field without a problem. However if the 2nd field is null then
it is returning a null for the 3rd field. I have checked and the field
that I am linking to is null also.
>
So if I have
>
select t1.field1, t1.field2, t2.field1, t2.field2, t2.field3
from table1 t1
join table2 t2
on t1.field1=t2.field1 and t1.field2=t2.field2
>
with 2 records in each table
table1: record1: data, data
record2: data, null
table2: record1: data,data,data
record2: data,null,data
>
what I get from the script is
record1: data, data,data,data,data
record2: data,null,data,null,null
>
>
I would expect
record2: data,null,data,null,data
Please use sample data like 'A', 'B', 'C', etc., instead of "data" which
is much more confusing.

Run some sanity checks on the data:

select * from table1 where field2 is null
select * from table1 where field2 = 'NULL'

select * from table2 where field2 is null
select * from table2 where field2 = 'NULL'

What tool are you using to pull the data? Some clients (e.g. Crystal
Reports) have a "convert nulls to empty values" option.
Sep 27 '06 #4
Ok I finally got it. I still don't know why that didn't work. But
here is what I did instead and it worked.
On the join I changed it to the following.

On t1.field1=t2.field1 and (case when t1.field2 is null then '' else
t1.field2 end)=(case when t2.field2 is null then '' else t2.field2 end)

mike wrote:
Oh yea, and one thing I forgot is that my join is a Left Outer, if I
make it an inner join the records with the NULL just don't show at all.
So the issue is within the linking.

mike wrote:
Yes using select statements like that does return the data where field2
is null.
This is just using query analyzer. This is part of a view that I am
making some changes to.
Also I have tried using the SET ANSI_NULLS ON and SET ANSI_NULLS OFF
and it made no difference.

Ed Murphy wrote:
mike wrote:
>
I have a new table that I am adding to a script that I wrote. This
table has 3 fields, the first 2 fields are used in the on statement as
being = other fields in the script.

The first field always has data in it, but the 2nd field is sometimes
null.

So my problem is if both fields have data in them and they both match
to the data in the fields that I am linking them to, then it returns
the 3rd field without a problem. However if the 2nd field is null then
it is returning a null for the 3rd field. I have checked and the field
that I am linking to is null also.

So if I have

select t1.field1, t1.field2, t2.field1, t2.field2, t2.field3
from table1 t1
join table2 t2
on t1.field1=t2.field1 and t1.field2=t2.field2

with 2 records in each table
table1: record1: data, data
record2: data, null
table2: record1: data,data,data
record2: data,null,data

what I get from the script is
record1: data, data,data,data,data
record2: data,null,data,null,null


I would expect
record2: data,null,data,null,data
>
Please use sample data like 'A', 'B', 'C', etc., instead of "data" which
is much more confusing.
>
Run some sanity checks on the data:
>
select * from table1 where field2 is null
select * from table1 where field2 = 'NULL'
>
select * from table2 where field2 is null
select * from table2 where field2 = 'NULL'
>
What tool are you using to pull the data? Some clients (e.g. Crystal
Reports) have a "convert nulls to empty values" option.
Sep 27 '06 #5
mike wrote:
Ok I finally got it. I still don't know why that didn't work.
Nulls are not considered equal to anything, not even other nulls.

http://en.wikipedia.org/wiki/Null_(SQL)
But here is what I did instead and it worked.
On the join I changed it to the following.

On t1.field1=t2.field1 and (case when t1.field2 is null then '' else
t1.field2 end)=(case when t2.field2 is null then '' else t2.field2 end)
Equivalent and shorter:

on t1.field1 = t2.field1
and coalesce(t1.field2,'') = coalesce(t2.field2,'')

coalesce() is a function that takes one or more argument and returns the
first non-null value among them, or null if they're all null.
Sep 28 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by Doug Baroter | last post: by
2 posts views Thread by James | last post: by
1 post views Thread by Brian | last post: by
4 posts views Thread by Rnt6872 | last post: by
1 post views Thread by CARIGAR | last post: by
1 post views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.