Hierarchy Query - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Hierarchy Query

Author

Topic

bscarl88
Starting Member

2 Posts

Posted - 2012-04-09 : 15:53:41

Hey all,
Having problems with Hierarchical querying.

We have our hierarchy table setup similar to this example

Columns:
ID(PK) | Continent | country | region | state

It is a table set for permissions so that someone may have permission to see data for the entire continent ( country, region, and state will be NULL) down to the specific state (all fields would be populated).

If someone is assigned Americas(ID = 1) they should be able to see all states/Providences (ID's 1-xxx).

If someone is assigned | America | USA | Northeast | NULL |
They should be able to see only data for USA States in the north east.

What I need help figuring out is a query that will allow me to organize distinct Continents/countries/regions/states.

I can't get Leaf hierarchies to work using sstart with... connect by...

Any help is appreciated. I know i just gave it a terrible explanation. Just let me know how I can go into more detail. I also can't figure out how to add a picture

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-04-10 : 05:44:49

Please post the DDL of your tables with some sample data...don't seem to understand the problem here.

Vinu Vijayan

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-04-10 : 11:32:12

I think what I would do is create a table that holds all the geographic info. Same structure as your permission table but without the person ID. It would contain all possible locations. Then you could use a query like this to get all relevant locations given one or more permission rows for a person:
Of course your tables will have proper keys, constraints, and referential integrity


select p.id
       ,h.continent
       ,h.country
       ,h.region
       ,g.state
from   permission p
inner join geoHierchary h
       on h.continent = p.continent
       and h.country = coalesce(p.country, h.country)
       and h.region = coalesce(p.region, h.region)
       and h.state = coalesce(p.state, h.state)
where  p.id = @someonesID

Be One with the Optimizer
TG

bscarl88
Starting Member

2 Posts

Posted - 2012-04-10 : 14:48:44

Awesome! Thanks!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-04-10 : 22:21:10

you're welcome

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources