Post reply

search levels

  • September 30, 2012 at 4:38 am

    #282324

    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

  • September 30, 2012 at 4:54 am

    #1543734

    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

  • September 30, 2012 at 8:35 am

    #1543736

    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

  • September 30, 2012 at 12:19 pm

    #1543757

    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

  • September 30, 2012 at 12:34 pm

    #1543761

    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

  • September 30, 2012 at 1:18 pm

    #1543762

    hi J Livingston,

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

    Mike

  • October 1, 2012 at 6:06 am

    #1543936

    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...

  • October 1, 2012 at 6:17 am

    #1543940

    Bit of a duplicate post?

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

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply