Putting a condition on a subgroup to query result

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

Transact-SQL (2008)

Putting a condition on a subgroup to query result

Author

Topic

yingchai
Starting Member

33 Posts

Posted - 2011-11-11 : 04:15:15

Hi,

I have a organization table below which shows the relationship between a parent and child relationship of a organization hierarchy.

[Organization table]


Parent       Organization
-------------------------
             Sunway Group
Sunway Group 10000000
Sunway Group SUNH GROUP
SUNH GROUP   21000000
SUNH GROUP   22000000
SUNH GROUP   23000000

I have a fact table which links to the organization code. Now, I intend to select all organizations from the fact table by only using 'Sunway Group' as the keyword.

Example:
select * from fact_table where organization in ('2100000','2200000','2300000')

which is equivalent to: select * from fact_table where organization = 'Sunway Group'

but the problem is that the fact table only stores base level organization members.

So, my question is how to select all organizations from the fact table by only using 'Sunway Group' as the keyword?

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-11 : 04:19:37

[code]
;With CTE(Organisation,Parent,Level)
AS
(SELECT Organization,NULL,1
FROM [Organization table]
WHERE Organization='Sunway Group'
UNION ALL
SELECT o.Organization,o.Parent,c.Level+1
FROM [Organization table] o
INNER JOIN CTE c
ON c.Organization=o.Parent
)

SELECT Organisation
FROM CTE
ORDER BY Level
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

yingchai
Starting Member

33 Posts

Posted - 2011-11-11 : 05:19:26

Hi visakh,

i had execute the script below:


;With CTE(Organization,Parent,Level)
AS
(SELECT Organization,NULL,1
FROM org_hrchy_details
WHERE Organization='Sunway Group'
UNION ALL
SELECT o.Organization,o.Parent,c.Level+1
FROM org_hrchy_details o
INNER JOIN CTE c
ON c.Organization=o.Parent
)

SELECT Organization
FROM CTE
ORDER BY Level

But received this error:

quote:

Msg 240, Level 16, State 1, Line 1
Types don't match between the anchor and the recursive part in column "Parent" of recursive query "CTE".

Kindly advise. Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-11 : 06:05:30

[code]
;With CTE(Organization,Parent,Level)
AS
(SELECT Organization,CAST(NULL AS varchar(2000)),CAST(1 AS int)
FROM org_hrchy_details
WHERE Organization='Sunway Group'
UNION ALL
SELECT o.Organization,CAST(o.Parent AS varchar(2000)),c.Level+1
FROM org_hrchy_details o
INNER JOIN CTE c
ON c.Organization=o.Parent
)

SELECT Organization
FROM CTE
ORDER BY Level

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources