See if these links help.

http://www.help-sql.info/27/3/1120124.html

http://consultingblogs.emc.com/jamiethomson/archive/2007/03/28/SSIS_3A00_-Building-Packages-Programatically.aspx

Your are right and you code does get my desired results. Simpler, faster, better...Thanks again.

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=...

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...

Thanks to those who responded, it was educational. Pursuing hierarchical processing is beyond my scope right now so I'd like to close the thread.

My employer strongly discourages...

Thnaks All. I was able to reach a suitable solution using the CASE WHEN EXISTS syntax!

John Rowan (10/27/2010)

---

Thanks Gus, your correct.

Chrissy321, You mentioned that you can't put the flag at the parent level because application/security reasons. Rarely have I seen where outside factors like...

GSquared (10/27/2010)

---

Your solution will work if the hierarchy only has two levels. To be precise, it will work if a child can't have...

I haven't looked at a hierarchy model. The child to parent ratio is fairly low, 5:1 on average.

I was thinking I could do an existence check in a subquery.

pseudo...

I tried this using linked server and had to terminate the query after 5 minutes. My table has 75 million rows.

I added WHERE clauses and then ran...

If to put the code populating table EmpChart nto a trigger on table Emp then every change in the Org Chart will be reflected immediately in EmpChart and ready for...

Thanks all. Yes I am still using 2000 (or at least my vendor is). This definitely is a bigger can of worms than I first thought!

I am going to try...