Issue with missing data / NULLS

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

Transact-SQL (2012)

Issue with missing data / NULLS

Author

Topic

jonclayIRM
Starting Member

12 Posts

Posted - 2015-03-23 : 08:49:41

Hi there

I have a problem trying to get the following code to work correctly:

SELECT DISTINCT Contacts.ContactID, Contacts.CategoryCode, Addresses.Country, Addresses.DateCreated, Attributes.AttributeCode

FROM dbo.Contacts

FULL OUTER JOIN dbo.Addresses on Contacts.ContactID=Addresses.ContactID
FULL OUTER JOIN dbo.Attributes ON Contacts.ContactID=Attributes.ContactID

WHERE Contacts.CategoryCode IN ('SCT','SDP') and Deleted =0 AND
Addresses.DateCreated >='01/10/14' AND Addresses.DateCreated <='01/02/15' AND
Attributes.AttributeTypeID=29

ORDER BY Contacts.ContactID

My issue is that I want to show Attributes.AttributeCode where Attributes.AttributeType=29 - pretty easy and that's what I'm doing. However, there is also the potential for Attributes.AttributeType=29 to not be there at all, and I also want to show these records too. So, basically show all records as specified with Attributes.AttributeType=29 or where Attributes.AttributeType is missing.

I hope this makes sense! Many thanks for your help.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-23 : 08:54:30

If there is no match on the Attributes table, then because you are doing a full outer join, the Attributes.AttributeTypeID column will be NULL,so you can extend your WHERE

WHERE ...
Attributes.AttributeTypeID=29 or Attributes.AttributeTypeID is NULL

jonclayIRM
Starting Member

12 Posts

Posted - 2015-03-23 : 08:56:35

Excellent, thank you! I hoped it would be as simple as that!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources