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


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:40 PM
Points: 2,082, Visits: 22,056
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
and remember....every day is a school day
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 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


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:40 PM
Points: 2,082, Visits: 22,056
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
and remember....every day is a school day
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 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: 2 days ago @ 10:40 AM
Points: 540, Visits: 1,187
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: Thursday, April 23, 2015 2:52 AM
Points: 393, Visits: 1,223
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