Problem with information not present (not 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)

Problem with information not present (not nulls)

Author

Topic

jonclayIRM
Starting Member

12 Posts

Posted - 2015-03-24 : 09:32:32

Hello

I have a problem whereby I want to create a report to pull out rows where data does not exist (not where a NULL is present as such).

Basically, I have an AttributeTypeID column in the Attributes table. I am interested in pulling out all records where AttributeTypeID=29 or where AttributeTypeID 29 is missing. All records should have this AttributeTypeID of 29 but some don't due to human inputting error.

The SQL that I'm using is shown below. I can see why it doesn't work, but I don't know how to fix it. As you'll see, the output is showing everyone with AttributeTypeID=29 AND everyone where there are no AttributeTypeIDs present at all.

I hope this makes sense!

SELECT DISTINCT Contacts.ContactID, Contacts.CategoryCode, Contacts.CompanyName, Attributes.AttributeCode, Attributes.AttributeTypeID

FROM dbo.Contacts

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

WHERE (Attributes.AttributeTypeID=29 or Attributes.AttributeTypeID is NULL)
AND Deleted=0

Many thanks
Jon

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-24 : 10:15:57

post some sample input data and desired results

Ifor
Aged Yak Warrior

700 Posts

Posted - 2015-03-24 : 10:35:39

Maybe something like the following, otherwise post some test data, in a consumable format, as suggested.


SELECT C.ContactID, C.CategoryCode, C.CompanyName, A.AttributeCode, A.AttributeTypeID
FROM dbo.Contacts C
	LEFT JOIN dbo.Attributes A
		ON C.ContactID = A.ContactID
			AND A.AttributeTypeID=29;

jonclayIRM
Starting Member

12 Posts

Posted - 2015-03-24 : 11:20:16

Thanks for your replies. I have to attend a meeting now but will post some input data in the morning.
Much appreciated.
Jon

jonclayIRM
Starting Member

12 Posts

Posted - 2015-03-25 : 05:16:29

Hi there

Thank you for your patience with me on this. Please see below for some data as suggested.

The database contains various AttributeTypeID which have a variety of AttributeCode attached to them as can be seen below. What I want to achieve is all records within the database where they have an AttributeTypeID=29 or where AttributeTypeID is missing.

ContactID CategoryCode AttributeCode AttributeTypeID
00012998 ST ERM 14
00013221 ST FSE 14
00013235 ST EGY 14
00013332 ST SV 14
00013883 ST OR 14
00014014 ST OR 14
00000177 AFF CONS 29
00000243 AFF INS 29
00000247 AFF TL 29
00000434 AFF CONS 29
00000510 AFF ENGY 29
00000791 AFF INS 29
00000815 AFF TL 29

SELECT DISTINCT Contacts.ContactID, Contacts.CategoryCode, Contacts.CompanyName, Attributes.AttributeCode, Attributes.AttributeTypeID

FROM dbo.Contacts

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

WHERE (Attributes.AttributeTypeID IN (14,29) or Attributes.AttributeTypeID is NULL)
AND Deleted=0
AND Contacts.CategoryCode IN ('AFF','AFFO','CRT','FEL','GRA','GSM','HFE','HLM','MEM','SCT','SDP','SFS','SP','SSP','ST')

ORDER BY Attributes.AttributeTypeID ASC, Contacts.CategoryCode, Contacts.ContactID

Many thanks
Jon

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-25 : 09:14:16

Your query should work, though you might need to adjust the last condition:

[code]

AND (Contacts.CategoryCode IN ('AFF','AFFO','CRT','FEL','GRA','GSM','HFE','HLM','MEM','SCT','SDP','SFS','SP','SSP','ST')
OR Contacts.CategoryCode is NULL)

jonclayIRM
Starting Member

12 Posts

Posted - 2015-03-25 : 09:51:21

Thank you, but the Contacts.CategoryCode is never NULL. It's the A.AttributeTypeID=29 that may be missing.

The problem I have with my original SQL statement is that if I use the NULL function then the output includes any people who don't have any AttributeTypeID's at all, not just ones where 29 is missing.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-25 : 11:12:40

Oh then don't use full join. Try left join instead

jonclayIRM
Starting Member

12 Posts

Posted - 2015-03-25 : 11:25:08

Unfortunately this still doesn't work :(

I have a ContactID that doesn't have A.AttributeTypeID=29 but does have e.g. A.AttributeTypeID=12, so my original SQL statement doesn't work in this situation because it is only asking for those contacts with A.AttributeTypeID=29 or those where A.AttributeTypeID=NULL.

I hope this makes sense :)

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-25 : 11:36:11

Wait a minute! you just said:

" ... includes any people who don't have AttributeTypeID's at all, not just ones where 29 is missing."

what is the difference between "don't have AttributeTypeID's at all" and "where 29 is missing"?

I assumed that "don't have AttributeTypeID's at all" means AttributeTypeID is NULL and "where 29 is missing" means AttributeTypeID <> 29

is that what you mean?

jonclayIRM
Starting Member

12 Posts

Posted - 2015-03-25 : 12:19:49

Sorry, I'm probably explaining this really badly :)

Every contact SHOULD have A.AttributeTypeID=29 but some don't. Without trawling manually through the database I need to find out which contacts don't have A.AttributeTypeID=29 (i.e. those who don't I'm calling missing).

Some contacts have A.AttributeTypeID=something else (e.g. 14, 15, 16, 22 etc). I'm not bothered about these at all.

All I need to achieve is a list solely of contacts who have A.AttributeTypeID=29 and those that don't have it (i.e. those where A.AttributeTypeID=29 is missing from the record).

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-25 : 12:34:46

But:

"list solely of contacts who have A.AttributeTypeID=29 and those that don't have it (i.e. those where A.AttributeTypeID=29 is missing from the record)."

would be, by definition, everyone. That is, for any row, either AttributeTypeID = 29 or AttributeTypeID <> 29 or AttributeTypeID is NULL. The last two conditions "don't have AttributeTypeID = 29"

by the way you should probably stop using the word "missing" this way. That is, the statement "AttributeTypeID = 12" implies "AttributeTypeID <> 29". to say "AttributeTypeID=29 is missing" is really confusing, at least to me

Ifor
Aged Yak Warrior

700 Posts

Posted - 2015-03-25 : 12:40:26

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2015-03-25 : 18:20:35

This will list everything, which seems to be what you want ?! If you want to list only Contacts that don't have AttributeTypeID = 29, let me know.


SELECT c.ContactID, c.CategoryCode, c.CompanyName, a.AttributeCode, a.AttributeTypeID

FROM dbo.Contacts c

LEFT OUTER JOIN dbo.Attributes a ON c.ContactID=a.ContactID AND a.AttributeTypeID IN (14,29)

WHERE
    c.Deleted=0
AND c.CategoryCode IN ('AFF','AFFO','CRT','FEL','GRA','GSM','HFE','HLM','MEM','SCT','SDP','SFS','SP','SSP','ST')

ORDER BY a.AttributeTypeID ASC, c.CategoryCode, c.ContactID

jonclayIRM
Starting Member

12 Posts

Posted - 2015-03-26 : 04:58:05

Thank you Scott. That seemed to work fine :)

However, I would be interested in also seeing what you proposed about just those without 29.

Many thanks
Jon

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2015-03-26 : 11:03:34

[code]
SELECT c.ContactID, c.CategoryCode, c.CompanyName, a.AttributeCode, a.AttributeTypeID

FROM dbo.Contacts c

WHERE
c.Deleted=0
AND c.CategoryCode IN ('AFF','AFFO','CRT','FEL','GRA','GSM','HFE','HLM','MEM','SCT','SDP','SFS','SP','SSP','ST')
AND NOT EXISTS(SELECT 1 FROM dbo.Attributes a
WHERE c.ContactID=a.ContactID
AND a.AttributeTypeID IN ('29'))

ORDER BY a.AttributeTypeID ASC, c.CategoryCode, c.ContactID

[/code]

jonclayIRM
Starting Member

12 Posts

Posted - 2015-03-26 : 12:29:40

Many thanks to both of you. You have been very helpful.

Best wishes
Jon

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources