InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 22:56:40 GMT20http://www.sqlservercentral.com/Forums/Topic1366246-392-1.aspxBit of a duplicate post?[url]http://www.sqlservercentral.com/Forums/Topic1366265-3077-1.aspx[/url]Mon, 01 Oct 2012 06:17:27 GMTlaurie-789651http://www.sqlservercentral.com/Forums/Topic1366246-392-1.aspxHi,Use a CTE[code="sql"]DECLARE @RegionId INT = 1 ;WITH RegionList AS ( select t.RegionId, t.RegionType, t.RegionName, t.ParentRegionID from ParentRegionList t where RegionID = @RegionId union all select t0.RegionId, t0.RegionType, t0.RegionName, t0.ParentRegionID from ParentRegionList t0 inner join RegionList t1 on t0.ParentRegionID = t1.RegionID ) SELECT * FROM RegionList [/code]This will give all the regions under Europe (@RegionId = 1).Change the regionId to get whatever regions / sub-regions you want.PedroMon, 01 Oct 2012 06:06:42 GMTPiManéhttp://www.sqlservercentral.com/Forums/Topic1366246-392-1.aspx[quote]Your code looks a little bit complex for me so I will study on it.Hope i get my project on wheels...[/quote]Might want to get a copy of TREES & HIERARCHIES IN SQL.Sun, 30 Sep 2012 13:38:11 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic1366246-392-1.aspxhi J Livingston,That article helps me a lot.. Thanks for your suggestion!!MikeSun, 30 Sep 2012 13:18:29 GMTMike Saunders NLhttp://www.sqlservercentral.com/Forums/Topic1366246-392-1.aspxHi Miketake a look at this article by Jeff Moden...may give you a few ideas[b][url]http://www.sqlservercentral.com/articles/T-SQL/72503/[/url][/b]Sun, 30 Sep 2012 12:34:49 GMTJ Livingston SQLhttp://www.sqlservercentral.com/Forums/Topic1366246-392-1.aspxHi CELKO,Thanks for your reply. Your code looks a little bit complex for me so I will studie on it.Hope i get my project on wheels...MikeSun, 30 Sep 2012 12:19:34 GMTMike Saunders NLhttp://www.sqlservercentral.com/Forums/Topic1366246-392-1.aspxI built this kind of table for uShip. You need to start with this nested sets model and then write procedure to insert subtrees. Continents and other units have ISO codes. INSERT INTO Geography_Nested_SetsVALUES ('World', 'World', 'WLD', 1, 16), ('Africa', 'AF', 'CNT', 2, 3), ('Antarctica', 'AN', 'CNT', 4, 5), ('Asia', 'AS', 'CNT', 6, 7), ('Europe', 'EU', 'CNT', 8, 9), ('North America', 'NA', 'CNT', 10, 11), ('South America', 'SA', 'CNT', 12, 13), ('Oceania', 'OC', 'CNT', 14, 15);Here is a skeleton for the insertion proc CREATE TABLE Tree(node_name VARCHAR(15) NOT NULL, lft INTEGER NOT NULL CHECK (lft > 0) UNIQUE, rgt INTEGER NOT NULL CHECK (rgt > 0) UNIQUE, CHECK (lft < rgt));CREATE PROCEDURE InsertChildrenIntoTree(@in_root_node VARCHAR(15) = NULL, @in_child_01 VARCHAR(15) = NULL, @in_child_02 VARCHAR(15) = NULL, @in_child_03 VARCHAR(15) = NULL, @in_child_04 VARCHAR(15) = NULL, @in_child_05 VARCHAR(15) = NULL, @in_child_06 VARCHAR(15) = NULL, @in_child_07 VARCHAR(15) = NULL, @in_child_08 VARCHAR(15) = NULL, @in_child_09 VARCHAR(15) = NULL, @in_child_10 VARCHAR(15) = NULL)ASBEGIN -- Find the parent node of the new subtreeDECLARE @local_parent_rgt INTEGER;SET @local_parent_rgt = (SELECT rgt FROM Tree WHERE node_name = @in_root_node);--put the children into kindergarten SELECT node_name, (lft + @local_parent_rgt -1) AS lft, (rgt + @local_parent_rgt -1) AS rgt INTO #local_kindergarten FROM (VALUES (@in_child_01, 1, 2), (@in_child_02, 3, 4), (@in_child_03, 5, 6), (@in_child_04, 7, 8), (@in_child_05, 9, 10), (@in_child_06, 11, 12), (@in_child_07, 13, 14), (@in_child_08, 15, 16), (@in_child_09, 17, 18), (@in_child_10, 19, 20)) AS Kids (node_name, lft, rgt) WHERE node_name IS NOT NULL; --use the size of the kindergarten to make a gap UPDATE Tree SET lft = CASE WHEN lft > @local_parent_rgt THEN lft + (2 * (SELECT COUNT(*) FROM #local_kindergarten)) ELSE lft END, rgt = CASE WHEN rgt >= @local_parent_rgt THEN rgt + (2 * (SELECT COUNT(*) FROM #local_kindergarten)) ELSE lft END WHERE lft > @local_parent_rgt OR rgt >= @local_parent_rgt; INSERT INTO Tree (node_name, lft, rgt)SELECT node_name, lft, rgt FROM #local_kindergarten; SELECT * FROM Tree; END;GOEXEC InsertChildrenIntoTree 'Global', 'USA','Canada','Europe', 'Asia';EXEC InsertChildrenIntoTree 'USA','Texas', 'Georgia', 'Utah', 'New York', 'Maine', 'Alabama';Sun, 30 Sep 2012 11:50:10 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic1366246-392-1.aspxCREATE TABLE [ParentRegionList]( [RegionID] [int] NOT NULL, [RegionType] [nvarchar](50) NULL, [RegionName] [nvarchar](255) NULL, [ParentRegionID] [int] NULL)INSERT INTO [ParentRegionList] ([RegionID],[RegionType],[RegionName],[ParentRegionID]) VALUES (1,'Continent','Europe',0)INSERT INTO [ParentRegionList] ([RegionID],[RegionType],[RegionName],[ParentRegionID]) VALUES (2,'Continent','North America',0)INSERT INTO [ParentRegionList] ([RegionID],[RegionType],[RegionName],[ParentRegionID]) VALUES (500,'Country','The Netherlands',1)INSERT INTO [ParentRegionList] ([RegionID],[RegionType],[RegionName],[ParentRegionID]) VALUES (501,'Country','Germany',1)INSERT INTO [ParentRegionList] ([RegionID],[RegionType],[RegionName],[ParentRegionID]) VALUES (502,'Country','Belgium',1)INSERT INTO [ParentRegionList] ([RegionID],[RegionType],[RegionName],[ParentRegionID]) VALUES (550,'Country','United States of America',2)INSERT INTO [ParentRegionList] ([RegionID],[RegionType],[RegionName],[ParentRegionID]) VALUES (800,'State','New York',550)INSERT INTO [ParentRegionList] ([RegionID],[RegionType],[RegionName],[ParentRegionID]) VALUES (1000,'City','New York',800)INSERT INTO [ParentRegionList] ([RegionID],[RegionType],[RegionName],[ParentRegionID]) VALUES (1001,'City','Amsterdam',500)INSERT INTO [ParentRegionList] ([RegionID],[RegionType],[RegionName],[ParentRegionID]) VALUES (1002,'City','Rotterdam',500)INSERT INTO [ParentRegionList] ([RegionID],[RegionType],[RegionName],[ParentRegionID]) VALUES (1003,'City','Brussels',502)INSERT INTO [ParentRegionList] ([RegionID],[RegionType],[RegionName],[ParentRegionID]) VALUES (5000,'Neighborhood','Amsterdam Center',1001)INSERT INTO [ParentRegionList] ([RegionID],[RegionType],[RegionName],[ParentRegionID]) VALUES (5001,'Neighborhood','The Pijp',1001)INSERT INTO [ParentRegionList] ([RegionID],[RegionType],[RegionName],[ParentRegionID]) VALUES (10001,'Street','Damrak',5000)INSERT INTO [ParentRegionList] ([RegionID],[RegionType],[RegionName],[ParentRegionID]) VALUES (10002,'Street','Rue de Brussels',1003)In this example the search variable 1 (Europe) has to show all records that are (in)direct (top down) connected to RegionID 1 so:500 The Netherlands501 Germany502 Belgium1001 Amsterdam1002 Rotterdam1003 Brussels5000 Amsterdam Center5001 The Pijp10001 Damrak10002 Rue de BrusselsIn this example the search variable 800 (state new york) has to show all records that are (in)direct (top down) connected to RegionID 800 so:1000 New YorkIn this example the search variable 1001 (Amsterdam) has to show all records that are (in)direct (top down) connected to RegionID 1001 so:5000 Amsterdam Center5001 The Pijp10001 DamrakSun, 30 Sep 2012 08:35:13 GMTMike Saunders NLhttp://www.sqlservercentral.com/Forums/Topic1366246-392-1.aspxwould you care to provide some set up scripts (create table / sample data) this will help us to clarify your question.thanksSun, 30 Sep 2012 04:54:13 GMTJ Livingston SQLhttp://www.sqlservercentral.com/Forums/Topic1366246-392-1.aspxHello all,I'm wondering if it is possible to do the following. I have a table with locations in the world.All locations have the locationtype continent, country, state, city, neigbourhood or street.Also i have a table that tell contains all parent locations of a location (e.g. the parent location of a state is a country) That table looks like:LocationID, Locationname, ParentlocationIDThe parentlocationID refers to the locationID of the parent Location.Now I want to make a query that shows me all underlying locations of a given location. E.g. when i search for a location that is a country i get all states, cities, neigbourhoods and streets for that country. But when i search for a city it will show only all neighbourhoods and streets.Does somebody know how to solve this?Any suggestions are welcome!!Thanks!!MikeSun, 30 Sep 2012 04:38:25 GMTMike Saunders NL