search levels

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » search levels

search levels

Rate Topic

Display Mode

Topic Options

Author

Message

Mike Saunders NL

Posted Sunday, September 30, 2012 4:38 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, March 13, 2013 1:06 PM
Points: 14, Visits: 68

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

J Livingston SQL

Posted Sunday, September 30, 2012 4:54 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Today @ 4:04 AM
Points: 1,456, Visits: 14,259

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

Mike Saunders NL

Posted Sunday, September 30, 2012 8:35 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, March 13, 2013 1:06 PM
Points: 14, Visits: 68

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

CELKO

Posted Sunday, September 30, 2012 11:50 AM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 11:11 AM
Points: 1,945, Visits: 2,782

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

Mike Saunders NL

Posted Sunday, September 30, 2012 12:19 PM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, March 13, 2013 1:06 PM
Points: 14, Visits: 68

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

J Livingston SQL

Posted Sunday, September 30, 2012 12:34 PM

UDP Broadcaster

Group: General Forum Members
Last Login: Today @ 4:04 AM
Points: 1,456, Visits: 14,259

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

Mike Saunders NL

Posted Sunday, September 30, 2012 1:18 PM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, March 13, 2013 1:06 PM
Points: 14, Visits: 68

hi J Livingston,

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

Mike

Post #1366290

CELKO

Posted Sunday, September 30, 2012 1:38 PM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 11:11 AM
Points: 1,945, Visits: 2,782

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

PiMané

Posted Monday, October 01, 2012 6:06 AM

SSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 5:02 AM
Points: 403, Visits: 904

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

laurie-789651

Posted Monday, October 01, 2012 6:17 AM

SSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 8:56 AM
Points: 274, Visits: 787

Bit of a duplicate post?

http://www.sqlservercentral.com/Forums/Topic1366265-3077-1.aspx

Post #1366513

« Prev Topic | Next Topic »

Permissions