July 12, 2013 at 6:04 am
Hi
I am trying to write a script to get all record where the AccountIDName
is not equal to some companies, status = 1 and stc.value = 'F - Family'
This part works for me...
However... I also need to grab records where
status=1, stc.value = 'F - Family' and the AccountIDName is null
I can't seem to figure out how to write this part.,
Below is my code:
SELECT
CON.AccountIdName,
CON.FirstName,
CON.LastName,
STC.Value
FROM dbo.Contact CON
LEFT OUTER JOIN StringMap STC
ON STC.attributename = 'new_coveragetype'
AND STC.attributevalue = CON.new_CoverageType
AND STC.objecttypecode = 2
AND STC.langid = 1033
WHERE CON.StatusCode= 1
AND STC.Value = 'F - Family'
AND CON.AccountIdName NOT IN
(
'Company1',
'Company2',
'Company3',
'Company4',
'Company5,
'Company6'
)
July 12, 2013 at 6:13 am
Does this work?
SELECT
CON.AccountIdName,
CON.FirstName,
CON.LastName,
STC.Value
FROM dbo.Contact CON
LEFT OUTER JOIN StringMap STC
ON STC.attributename = 'new_coveragetype'
AND STC.attributevalue = CON.new_CoverageType
AND STC.objecttypecode = 2
AND STC.langid = 1033
WHERE CON.StatusCode= 1
AND STC.Value = 'F - Family'
AND (
CON.AccountIdName NOT IN
(
'Company1',
'Company2',
'Company3',
'Company4',
'Company5',
'Company6'
)
OR CON.AccountIdName IS NULL)
);
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 12, 2013 at 6:18 am
Hi Kath, welcome to ssc!
-- unless you are checking for NULL,
-- referencing a column from an OUTER-joined table
-- will turn the join into an INNER join
-- Here's the original query:
SELECT
CON.AccountIdName,
CON.FirstName,
CON.LastName,
STC.Value
FROM dbo.Contact CON
LEFT OUTER JOIN StringMap STC
ON STC.attributename = 'new_coveragetype'
AND STC.attributevalue = CON.new_CoverageType
AND STC.objecttypecode = 2
AND STC.langid = 1033
WHERE CON.StatusCode = 1
AND STC.Value = 'F - Family'
AND CON.AccountIdName NOT IN
(
'Company1',
'Company2',
'Company3',
'Company4',
'Company5',
'Company6'
)
-- Here's how it should be written to preserve the outer join:
SELECT
CON.AccountIdName,
CON.FirstName,
CON.LastName,
STC.Value
FROM dbo.Contact CON
LEFT OUTER JOIN StringMap STC
ON STC.attributename = 'new_coveragetype'
AND STC.attributevalue = CON.new_CoverageType
AND STC.objecttypecode = 2
AND STC.langid = 1033
AND STC.Value = 'F - Family'
WHERE CON.StatusCode = 1
AND CON.AccountIdName NOT IN
(
'Company1',
'Company2',
'Company3',
'Company4',
'Company5',
'Company6'
)
-- check both queries and decide which is correct
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 12, 2013 at 6:19 am
Perfect 🙂
Thanks you made my day.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply