Joins when fields possibly NULL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Joins when fields possibly NULL

Author

Topic

JCirocco
Constraint Violating Yak Guru

392 Posts

Posted - 2009-08-12 : 15:09:11

I am hoping that I came to the right forumn and someone here has an idea that may help me.

I am trying to Left Outer Join a table back to itself on 2 fields where one of the joined fields MAY possibly be NULL. When I do, the sub-selected data does not include the records where one of the fields was NULL. The field that is NULL is not a required field.
The platform is SQL Server 2005.

The SQL:
SELECT LD1.CreatedBy,
LD1.ReferredBy,
LD1.[Total Leads],
LD2.[Total Leisure]

FROM (SELECT
CreatedBy,
ReferredBy,
COUNT(C_Lead_State) AS [Total Leads]
FROM Leads
GROUP BY
CreatedBy,
ReferredBy)
AS LD1

LEFT OUTER JOIN
(SELECT
CreatedBy,
ReferredBy,
COUNT(C_Lead_State) AS [Total Leisure]
FROM Leads AS Lead_2
WHERE (Type = 'Business')
GROUP BY
CreatedBy,
ReferredBy)
AS LD2
ON LD1.CreatedBy = LD2.CreatedBy
AND LD1.ReferredBy = LD2.ReferredBy

The sample "data":
CreatedBy,ReferredBy,Type
John,NULL,Business
John,NULL,Insurance
John,Dave,Business
Fred,NULL,Insurance
Fred,NULL,Business
Fred,John,Business

In my case, the times when there is no Referred By User those records do not get included on the joined ersion of the data.

Is there another way to join fields when the joining fields are both NULL? I have been at this for 3 days now...

Thanks - John

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-12 : 15:24:49

try this for your JOIN ON clause:

AND isNull(LD1.ReferredBy, 'null') = isNull(LD2.ReferredBy, 'null')

if ReferredBy is numeric then:

AND isNull(LD1.ReferredBy, -1) = isNull(LD2.ReferredBy, -1)

Be One with the Optimizer
TG

JCirocco
Constraint Violating Yak Guru

392 Posts

Posted - 2009-08-13 : 08:27:17

TG,

Thanks for the reply. I had never used the IsNull function before.

Unfortunately I simplified my example too much and left out one important detail. The two fields I am joining on are keys to another file and their data type for both are binary(8). So when I changed the lines as desribed above, I now get the message:

"Implicit conversion from data type varchar to binary is not allowed. Use the convert function to run this query."

I noticed that you had a different replace value for numeric so I took a chance and replaced your 'null' with the litteral 0x0000000000000000 and it worked perfectly!

Thanks again.
John

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources