October 28, 2010 at 10:21 am
Isn't this:
CASE WHEN EXISTS
(
SELECT 1
FROM Parent P
WHERE P.Parent_CD = Child.Parent_CD
AND P.Parent_CD IN
( SELECT DISTINCT P.Parent_CD
FROM Parent P
INNER JOIN Child C ON C.Parent_CD = P.Parent_CD
INNER JOIN ChildCustom AS CC ON C.Child_ID = CC.Child_ID
WHERE CC.SillyFlag= 'Y')
)
THEN 'MyCode'
the same as this:
CASE WHEN EXISTS
( SELECT 1
FROM Child C
INNER JOIN ChildCustom AS CC ON C.Child_ID = CC.Child_ID
WHERE CC.SillyFlag= 'Y' AND C.Parent_CD = Child.Parent_CD)
THEN 'MyCode'
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 28, 2010 at 10:40 am
This is a better representation, sorry about that.
So WHERE P.Parent_CD = MyTable.Parent_CD will refer one of the main tables in my SELECT.
Thanks for being willing to make suggestions when I don't have proper DDL and sample data.
CASE WHEN EXISTS
(
SELECT 1
FROM Parent P
WHERE P.Parent_CD = MyTable.Parent_CD
AND P.Parent_CD IN
( SELECT DISTINCT P.Parent_CD
FROM Parent P
INNER JOIN Child C ON C.Parent_CD = P.Parent_CD
INNER JOIN ChildCustom AS CC ON C.Child_ID = CC.Child_ID
WHERE CC.SillyFlag= 'Y')
)
THEN 'MyCode'
FROM MyTable
October 28, 2010 at 10:53 am
Which brings me to this....
SELECT
CASE WHEN EXISTS
(
SELECT 1
FROM Parent P
INNER JOIN Child C
ON MyTable.ParentID = P.ParentID
INNER JOIN ChildCustom CC
ON MyTable.ChildID = CC.ChildID
WHERE CC.SillyFlag= 'Y'
)
THEN 'MyCode'
ELSE NULL
END
FROM MyTable...
October 28, 2010 at 11:12 am
As an aside, when you use a sub-query for an "IN" statement, you don't need to use Distinct in it. It just slows it down, unless SQL Server figures out that it doesn't need it and ignores it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 28, 2010 at 12:47 pm
Chrissy321 (10/28/2010)
Which brings me to this....
SELECTCASE WHEN EXISTS
(
SELECT 1
FROM Parent P
INNER JOIN Child C
ON MyTable.ParentID = P.ParentID
INNER JOIN ChildCustom CC
ON MyTable.ChildID = CC.ChildID
WHERE CC.SillyFlag= 'Y'
)
THEN 'MyCode'
ELSE NULL
END
FROM MyTable...
Chrissy, I don't think this version will work. The correlation between the outer query and the inner query needs to be on ParentID alone, to the Child table in the inner query. The Child rows you're interested in all have the same ParentID for each row in the outer query. Now, I haven't tried correlating as you have shown, in the JOIN rather than the familiar WHERE clause, so I don't know if it works - but in this case I'm pretty sure it won't because you've also linked ChildID in the inner and outer queries. You lose the cardinality difference which makes the method work.
Try this instead:
SELECT
CASE WHEN EXISTS
(
SELECT 1
FROM Child C
INNER JOIN ChildCustom CC
ON CC.ChildID = C.ChildID
WHERE CC.SillyFlag= 'Y'
AND C.ParentID = MyTable.ParentID
)
THEN 'MyCode'
ELSE NULL
END
FROM MyTable...
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 28, 2010 at 12:48 pm
CELKO (10/28/2010)
"I can resist anything but temptation" -- Mae West
Commendable restraint, Joe.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 28, 2010 at 1:16 pm
Chrissy, I don't think this version will work.
Your are right and you code does get my desired results. Simpler, faster, better...Thanks again.
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply