473,387 Members | 1,423 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,387 software developers and data experts.

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 1679
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: Doug Baroter | last post by:
Hi, I've enherited a big mess, a SQL Server 2000 database with approximately 50 user tables and 65+ GB data, no explicit relationships among entities (RI constraints whatsover), attempt to...
2
by: James | last post by:
Can anyone please shed some light on the following... I have a framework that uses dynamically created tables, named using an incremental "attribute set ID", as follows: attrdata_1 attrdata_2...
2
by: orentini | last post by:
Microsoft Access MVP's and other good people - please help. I've posted here before. I'm having some weird problems with *some* ODBC linked tables on my MDB (Access XP), located on a file server...
1
by: Brian | last post by:
I need help joining info from two tables. Table1 and Table 2 have the same fields(Node,Card,Slot,Facility,Sub-Port,Channel,Group,Cic) Group is text, rest are number. Table 1 contains records for...
0
by: Larry Lard | last post by:
There seems to be something a bit lacking in the way the dataset designer thing deals (or rather doesn't) with nullable fields in VS2005. Maybe it's cos I'm using VB2005 Express (which is variously...
3
by: Reader | last post by:
Hello all, I am joining two tables in a query and the output should be all those records where two fields in table1 match two corresponding fields in table2. I joined the tables using both...
4
by: Rnt6872 | last post by:
Table A Table B BOL# B_BOL# Chargeback# Hi All, I have been struggling with this for...
8
by: DaFrizzler | last post by:
Hi, I have received the following email from a colleague, and am quite frankly baffled by the idea. I am just wondering if anyone has any advice or suggestions about this???? === BEGIN MAIL...
12
by: nyathancha | last post by:
Hi, I have a question regarding best practices in database design. In a relational database, is it wise/necessary to sometimes create tables that are not related to other tables through a...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.