A join to include NULL values - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

A join to include NULL values

Author

Topic

RudySQL
Starting Member

4 Posts

Posted - 2014-05-06 : 11:11:05

Hello All!

I have a join statement. The problem is that that the "UserID" in the CustomerCreditRep (ccr) table sometimes is NULL. I still need to include that row of data. I have a catch in the where statement that will do that. But, in the SELECT I'm pulling the UserLName based on a userID. But if there is no usserID, there is no user, so the row will not be included. How can I pull that row in, and give the value of "NA" if the userLname does not exist, because of a NULL value for the USERid?

Thanks

Rudy

select cr.AccountNumber, CreditLimit
,CreditLimitPerOrder
,TotalBalance
,CurrentBalance
,AmountOverdue1
,AmountOverdue2
,AmountOverdue3
,AmountOverdue4
,AmountOverdue5
,AmountOverdue6
,LastBookingDate
,PreviousInsertionDate
,LastPaymentDate
,LastPaymentAmount,ur.UserLname,
case cr.BookingStatusCode 
when 1 then 'DISABLED' 
when 0 then 'OK' 
else CONVERT(varchar(10),cr.BookingStatusCode)
end as  [BookingStatusCode] from customercreditinfo cci
join CustomerCreditRep ccr on ccr.AccountId = cci.AccountId and ccr.PrimaryCreditRep=1
join UsrUsers ur on ccr.UserId = ur.Userid
join Customer cr on cci.Accountid=cr.AccountId and cr.AccountNumber in (select vw_SDFC_Accounts_BulkData.Account_ID__c from vw_SDFC_Accounts_BulkData)
WHERE cr.TypeId =2 OR cr.TypeId =8 AND ccr.UserId IS NULL OR ccr.UserId IS NOT NULL

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-05-06 : 11:51:12

Can you post sample data in a consumable format to help illustrate the issue? Plus, we can run queries against it to give you tested code.

If you are not sure how to do that, here are some links that can help:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

RudySQL
Starting Member

4 Posts

Posted - 2014-05-06 : 13:47:48

Thanks for the reply Lamprey. I can not supply data at this time. I'm hoping I just have something written wrong.

Rudy

quote:
Originally posted by Lamprey

Can you post sample data in a consumable format to help illustrate the issue? Plus, we can run queries against it to give you tested code.

If you are not sure how to do that, here are some links that can help:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2014-05-06 : 14:18:43

This is a simple issue - you need to use an outer join on the UsrUsers table.

Change this:

join UsrUsers ur on ccr.UserId = ur.Userid

To this:

left join UsrUsers ur on ccr.UserId = ur.Userid

Remove this from the where clause - it is not needed (actually - it is causing rows to be returned that you don't want):

AND ccr.UserId IS NULL OR ccr.UserId IS NOT NULL

RudySQL
Starting Member

4 Posts

Posted - 2014-05-06 : 14:30:15

Hi Jeff! Perfect. It's been awhile since I have worked with SQL. I have a new gig, using it more. So I would i write the case to insert a value of "NA" or something when a NULL value is present?

Thanks

Rudy

RudySQL
Starting Member

4 Posts

Posted - 2014-05-06 : 14:46:04

Hello all.
I did get it. Thanks for the help. Here is final for reference.

select cr.AccountNumber, CreditLimit
,CreditLimitPerOrder
,TotalBalance
,CurrentBalance
,AmountOverdue1
,AmountOverdue2
,AmountOverdue3
,AmountOverdue4
,AmountOverdue5
,AmountOverdue6
,LastBookingDate
,PreviousInsertionDate
,LastPaymentDate
,LastPaymentAmount
,(ISNULL(ur.UserLname,'NA'))
,case cr.BookingStatusCode
when 1 then 'DISABLED'
when 0 then 'OK'
else CONVERT(varchar(10),cr.BookingStatusCode)
end as [BookingStatusCode] from customercreditinfo cci
join CustomerCreditRep ccr on ccr.AccountId = cci.AccountId and ccr.PrimaryCreditRep=1
left join UsrUsers ur on ccr.UserId = ur.Userid
join Customer cr on cci.Accountid=cr.AccountId and cr.AccountNumber in (select vw_SDFC_Accounts_BulkData.Account_ID__c from vw_SDFC_Accounts_BulkData)
WHERE cr.TypeId =2 OR cr.TypeId =8 order by AccountNumber

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources