locked
help me sort this sql query RRS feed

  • Question

  • User186897 posted

    I have got below table in sql...I have to divide this table and populaate in 2 diffrent treeviews...1 is location other team...how do I write a sql query for that

    OriginalUID   description        ParentUID
    
    1             Birmingham           19
    19            UnitedKingdom        31
    28            Kenya                33
    29            Nigeria              33
    33            Africa               34
    30            Asia                 34
    2             London               19
    31            Europe               34
    32            NorthAmerica         34
    34            The World             NULL
    10            Recruitment           3
    11            Payment               3
    14             Design               4
    15             Campaigns            4
    1             CE Office            NULL
    16              Pricing              2
    2 IT 1

    From above how can I change this below query which will populate only locations starting from The World..

    SELECT OriginalUID, description, ISNULL(OriginalParentUID,-1) As OriginalParentUID FROM PeopleF

    I want to change above query so that it takes all location and Parentnode would be "The World" and the 2nd treeview Parent node would be "CE Office"

    Monday, March 13, 2017 1:30 PM

Answers

  • User-1509636757 posted

    You should have one more column in this table to identify whether it is a team or it is a location (for example, IsLocation bit data type) and  your query will be very easy

    -- to fetch all location
    SELECT OriginalUID, description, ISNULL(OriginalParentUID,-1) As OriginalParentUID FROM PeopleF WHERE ISNULL(IsLocation, 0) = 1
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, March 13, 2017 1:40 PM
  • User-1509636757 posted

    Practically, I do not see anyway to get the expected result that you are aiming for, unless you have an identifying column whether it is a Location or a Team. Check below example, even if you try to get for example, Location with the condition 'The World', you will get Team records in between for matching Parent IDs:

    -- create a test table
    create table #SrcTable (OriginalUID int, description nvarchar(100), ParentUID int); 
    
    -- insert sample data
    insert into #SrcTable select 1 ,'Birmingham',19
    insert into #SrcTable select 19,'UnitedKingdom',31
    insert into #SrcTable select 28,'Kenya',33
    insert into #SrcTable select 29,'Nigeria',33
    insert into #SrcTable select 33,'Africa',34
    insert into #SrcTable select 30,'Asia',34
    insert into #SrcTable select 2 ,'London',19
    insert into #SrcTable select 31,'Europe',34
    insert into #SrcTable select 32,'NorthAmerica',34
    insert into #SrcTable select 34,'The World', NULL
    insert into #SrcTable select 10,'Recruitment', 3
    insert into #SrcTable select 11,'Payment', 3
    insert into #SrcTable select 14,' Design', 4
    insert into #SrcTable select 15,' Campaigns', 4
    insert into #SrcTable select 1 ,'CE Office',NULL
    insert into #SrcTable select 16,'Pricing',2
    insert into #SrcTable select 2 ,'IT',1;
    
    -- CTE to get the hierarhical Location data
    WITH MyCTE
    AS (SELECT OriginalUID, description, ParentUID
    FROM #SrcTable
    WHERE ParentUID IS NULL and description = 'The World'
    UNION ALL
    SELECT #SrcTable.OriginalUID, #SrcTable.description, #SrcTable.ParentUID
    FROM #SrcTable
    INNER JOIN MyCTE ON #SrcTable.ParentUID = MyCTE.OriginalUID
    WHERE #SrcTable.ParentUID IS NOT NULL)
    
    SELECT *
    FROM MyCTE;
    
    -- drop temp table
    drop table #SrcTable

    Output:

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, March 13, 2017 2:08 PM
  • User-1509636757 posted

    As I said, in earlier post, you may required to have an identification column if record is a Location or a Team, otherwise result may get affected by having wrong values of team inside location and vice versa.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, March 14, 2017 8:54 AM

All replies

  • User-1509636757 posted

    You should have one more column in this table to identify whether it is a team or it is a location (for example, IsLocation bit data type) and  your query will be very easy

    -- to fetch all location
    SELECT OriginalUID, description, ISNULL(OriginalParentUID,-1) As OriginalParentUID FROM PeopleF WHERE ISNULL(IsLocation, 0) = 1
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, March 13, 2017 1:40 PM
  • User186897 posted

    IsLocation there is no such column name..?and I am not allowed to make any changes to the table...this is the table given...

    Monday, March 13, 2017 1:44 PM
  • User-1509636757 posted

    Practically, I do not see anyway to get the expected result that you are aiming for, unless you have an identifying column whether it is a Location or a Team. Check below example, even if you try to get for example, Location with the condition 'The World', you will get Team records in between for matching Parent IDs:

    -- create a test table
    create table #SrcTable (OriginalUID int, description nvarchar(100), ParentUID int); 
    
    -- insert sample data
    insert into #SrcTable select 1 ,'Birmingham',19
    insert into #SrcTable select 19,'UnitedKingdom',31
    insert into #SrcTable select 28,'Kenya',33
    insert into #SrcTable select 29,'Nigeria',33
    insert into #SrcTable select 33,'Africa',34
    insert into #SrcTable select 30,'Asia',34
    insert into #SrcTable select 2 ,'London',19
    insert into #SrcTable select 31,'Europe',34
    insert into #SrcTable select 32,'NorthAmerica',34
    insert into #SrcTable select 34,'The World', NULL
    insert into #SrcTable select 10,'Recruitment', 3
    insert into #SrcTable select 11,'Payment', 3
    insert into #SrcTable select 14,' Design', 4
    insert into #SrcTable select 15,' Campaigns', 4
    insert into #SrcTable select 1 ,'CE Office',NULL
    insert into #SrcTable select 16,'Pricing',2
    insert into #SrcTable select 2 ,'IT',1;
    
    -- CTE to get the hierarhical Location data
    WITH MyCTE
    AS (SELECT OriginalUID, description, ParentUID
    FROM #SrcTable
    WHERE ParentUID IS NULL and description = 'The World'
    UNION ALL
    SELECT #SrcTable.OriginalUID, #SrcTable.description, #SrcTable.ParentUID
    FROM #SrcTable
    INNER JOIN MyCTE ON #SrcTable.ParentUID = MyCTE.OriginalUID
    WHERE #SrcTable.ParentUID IS NOT NULL)
    
    SELECT *
    FROM MyCTE;
    
    -- drop temp table
    drop table #SrcTable

    Output:

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, March 13, 2017 2:08 PM
  • User186897 posted

    Is there no other way I can sort this table to locations and teams and display in treeview?

    Tuesday, March 14, 2017 8:48 AM
  • User-1509636757 posted

    As I said, in earlier post, you may required to have an identification column if record is a Location or a Team, otherwise result may get affected by having wrong values of team inside location and vice versa.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, March 14, 2017 8:54 AM