Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

search levels Expand / Collapse
Author
Message
Posted Sunday, September 30, 2012 4:38 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 13, 2014 6:31 AM
Points: 23, Visits: 92
Hello 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, ParentlocationID

The 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!!

Mike
Post #1366246
Posted Sunday, September 30, 2012 4:54 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 10:40 PM
Points: 1,885, Visits: 18,473
would you care to provide some set up scripts (create table / sample data) this will help us to clarify your question.

thanks


__________________________________________________________________
you can lead a user to data....but you cannot make them think !
__________________________________________________________________
Post #1366250
Posted Sunday, September 30, 2012 8:35 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 13, 2014 6:31 AM
Points: 23, Visits: 92
CREATE 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 Netherlands
501 Germany
502 Belgium
1001 Amsterdam
1002 Rotterdam
1003 Brussels
5000 Amsterdam Center
5001 The Pijp
10001 Damrak
10002 Rue de Brussels

In 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 York

In 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 Center
5001 The Pijp
10001 Damrak
Post #1366257
Posted Sunday, September 30, 2012 11:50 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, July 20, 2014 11:55 AM
Points: 1,945, Visits: 2,860
I 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_Sets
VALUES
('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)
AS
BEGIN

-- Find the parent node of the new subtree
DECLARE @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;
GO

EXEC InsertChildrenIntoTree 'Global', 'USA','Canada','Europe', 'Asia';

EXEC InsertChildrenIntoTree 'USA','Texas', 'Georgia', 'Utah', 'New York', 'Maine', 'Alabama';



Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1366282
Posted Sunday, September 30, 2012 12:19 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 13, 2014 6:31 AM
Points: 23, Visits: 92
Hi 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...

Mike
Post #1366284
Posted Sunday, September 30, 2012 12:34 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 10:40 PM
Points: 1,885, Visits: 18,473
Hi Mike

take a look at this article by Jeff Moden...may give you a few ideas

http://www.sqlservercentral.com/articles/T-SQL/72503/


__________________________________________________________________
you can lead a user to data....but you cannot make them think !
__________________________________________________________________
Post #1366288
Posted Sunday, September 30, 2012 1:18 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 13, 2014 6:31 AM
Points: 23, Visits: 92
hi J Livingston,

That article helps me a lot.. Thanks for your suggestion!!

Mike
Post #1366290
Posted Sunday, September 30, 2012 1:38 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, July 20, 2014 11:55 AM
Points: 1,945, Visits: 2,860
Your code looks a little bit complex for me so I will study on it.
Hope i get my project on wheels...


Might want to get a copy of TREES & HIERARCHIES IN SQL.


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1366292
Posted Monday, October 1, 2012 6:06 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, June 12, 2014 9:30 AM
Points: 513, Visits: 1,129
Hi,

Use a CTE
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

This will give all the regions under Europe (@RegionId = 1).
Change the regionId to get whatever regions / sub-regions you want.

Pedro




If you need to work better, try working less...
Post #1366509
Posted Monday, October 1, 2012 6:17 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, June 27, 2014 8:03 AM
Points: 320, Visits: 1,079
Bit of a duplicate post?

http://www.sqlservercentral.com/Forums/Topic1366265-3077-1.aspx
Post #1366513
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse