locked
How can i do a self join on a table ? RRS feed

  • Question

  • User1253338400 posted

    Hi 

    I have data as follows in a table 

    NAME            FIELD                      TYPE            ENTITY


    Account         LastName           Guid              Contact
    Account         Name                  String            NULL
    Contact          LastName           String             NULL
    Contact          LastName         String             NULL
    Contract         ContractTerm     Integer           NULL
    Contract         StartDate           DateTime        NULL
    Contract         AccountId          Guid               Account
    Contract        Status                  String             NULL
    Opportunity  CloseDate           DateTime       NULL
    Opportunity  StageName         String             NULL
    Opportunity  Name                  String             NULL
    Order            Effective Date      DateTime     NULL
    Order            Name                   String             NULL
    Order            Status                   String            NULL
    Order            AccountId             Guid             Account


    How can i select all the rows that are Orders , so the last 4 but also the first 2 as they are Account and are linked to orders by the entity and also the two 
    contacts because they are linked to the Account via the Contact Entity in the first 
    Account row. Then stop as there are no links for contact ?

    The output should be 


    Order            Effective Date      DateTime     NULL
    Order            Name                   String             NULL
    Order            Status                   String            NULL
    Order            AccountId             Guid             Account

    Account         LastName           Guid              Contact
    Account         Name                  String            NULL
    Contact          LastName           String             NULL
    Contact          LastName         String             NULL

    So it is like a recursive selection which is governed by the entity column 

    Thanks

    Friday, September 6, 2019 5:04 AM

Answers

  • User-719153870 posted

    Hi robby32,

    I believe this requirement can be achieved. But a little question here, is it possible for your data to have a branch?

    Like below:

    NAME            FIELD                      TYPE            ENTITY


    Account         LastName           Guid              Contact
    Account         Name                  String            NULL
    Contact          LastName           String             NULL
    Contact          LastName         String             NULL
    Contract         ContractTerm     Integer           NULL
    Contract         StartDate           DateTime        NULL
    Contract         AccountId          Guid               Account
    Contract        Status                  String             NULL
    Opportunity  CloseDate           DateTime       NULL
    Opportunity  StageName         String             NULL
    Opportunity  Name                  String             NULL
    Order            Effective Date      DateTime     NULL
    Order            Name                   String             NULL
    Order            Status                   String            Opportunity 
    Order            AccountId             Guid             Account

    Or it's just a one-one-one-... format of data?

    If so, it will be much more easier. Please refer to below code:

    create table Snake(
    [NAME] varchar(50),
    [FIELD] varchar(50),
    [TYPE] varchar(50),
    [ENTITY] varchar(50)
    )
    
    insert into Snake values('Account','LastName','Guid','Contact')
    insert into Snake values('Account','Name','String','NULL')
    insert into Snake values('Contact','LastName','String','NULL')
    insert into Snake values('Contact','LastName','String','NULL')
    insert into Snake values('Contract','ContractTerm','Integer','NULL')
    insert into Snake values('Contract','StartDate','DateTime','NULL')
    insert into Snake values('Contract','AccountId','Guid','Account')
    insert into Snake values('Contract','Status','String','NULL')
    insert into Snake values('Opportunity','CloseDate','DateTime','NULL')
    insert into Snake values('Opportunity','StageName','String','NULL')
    insert into Snake values('Opportunity','Name','String','NULL')
    insert into Snake values('Order','Effective Date','DateTime','NULL')
    insert into Snake values('Order','Name','String','NULL')
    insert into Snake values('Order','Status','String','NULL')
    insert into Snake values('Order','LastName','Guid','Account')
    
    --select * from Snake
    --drop table Snake
    create table #Apple([ENTITY] varchar(50))
    go
    declare @sh varchar(50)
    declare @ap varchar(50)
    declare @s varchar(max)
    declare @i int
    declare @j int
    
    set @j=1
    set @sh='Order'-- you will only need to change this parameter to suit any other case
    set @s='(select * from Snake where NAME='''+@sh+''')'
    way:
    drop table #Apple
    select * into #Apple from (select [ENTITY] from Snake where NAME=@sh and [ENTITY]<>'NULL') as [ENTITY]
    
    set @i=1
    
    while @i<=(select COUNT([ENTITY]) from #Apple)
    begin
    set @ap=(select top 1 [ENTITY] from #Apple where [ENTITY] not in (select top (@i-1) [ENTITY] from #Apple ))
    set @s +='union all (select * from Snake where NAME='''+@ap +''')'
    set @sh=@ap
    set @i+=1
    end
    if (select COUNT([ENTITY]) from #Apple)>0 goto way;
    exec(@s)
    drop table #Apple
    drop table Snake

    And here's the result:

    Notice: this will also suit for the situation i mentioned up there(the branch one). It will output like below:

    Hope this can help.

    Best Regard,

    Yang Shen

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, September 6, 2019 6:31 AM

All replies

  • User-719153870 posted

    Hi robby32,

    I believe this requirement can be achieved. But a little question here, is it possible for your data to have a branch?

    Like below:

    NAME            FIELD                      TYPE            ENTITY


    Account         LastName           Guid              Contact
    Account         Name                  String            NULL
    Contact          LastName           String             NULL
    Contact          LastName         String             NULL
    Contract         ContractTerm     Integer           NULL
    Contract         StartDate           DateTime        NULL
    Contract         AccountId          Guid               Account
    Contract        Status                  String             NULL
    Opportunity  CloseDate           DateTime       NULL
    Opportunity  StageName         String             NULL
    Opportunity  Name                  String             NULL
    Order            Effective Date      DateTime     NULL
    Order            Name                   String             NULL
    Order            Status                   String            Opportunity 
    Order            AccountId             Guid             Account

    Or it's just a one-one-one-... format of data?

    If so, it will be much more easier. Please refer to below code:

    create table Snake(
    [NAME] varchar(50),
    [FIELD] varchar(50),
    [TYPE] varchar(50),
    [ENTITY] varchar(50)
    )
    
    insert into Snake values('Account','LastName','Guid','Contact')
    insert into Snake values('Account','Name','String','NULL')
    insert into Snake values('Contact','LastName','String','NULL')
    insert into Snake values('Contact','LastName','String','NULL')
    insert into Snake values('Contract','ContractTerm','Integer','NULL')
    insert into Snake values('Contract','StartDate','DateTime','NULL')
    insert into Snake values('Contract','AccountId','Guid','Account')
    insert into Snake values('Contract','Status','String','NULL')
    insert into Snake values('Opportunity','CloseDate','DateTime','NULL')
    insert into Snake values('Opportunity','StageName','String','NULL')
    insert into Snake values('Opportunity','Name','String','NULL')
    insert into Snake values('Order','Effective Date','DateTime','NULL')
    insert into Snake values('Order','Name','String','NULL')
    insert into Snake values('Order','Status','String','NULL')
    insert into Snake values('Order','LastName','Guid','Account')
    
    --select * from Snake
    --drop table Snake
    create table #Apple([ENTITY] varchar(50))
    go
    declare @sh varchar(50)
    declare @ap varchar(50)
    declare @s varchar(max)
    declare @i int
    declare @j int
    
    set @j=1
    set @sh='Order'-- you will only need to change this parameter to suit any other case
    set @s='(select * from Snake where NAME='''+@sh+''')'
    way:
    drop table #Apple
    select * into #Apple from (select [ENTITY] from Snake where NAME=@sh and [ENTITY]<>'NULL') as [ENTITY]
    
    set @i=1
    
    while @i<=(select COUNT([ENTITY]) from #Apple)
    begin
    set @ap=(select top 1 [ENTITY] from #Apple where [ENTITY] not in (select top (@i-1) [ENTITY] from #Apple ))
    set @s +='union all (select * from Snake where NAME='''+@ap +''')'
    set @sh=@ap
    set @i+=1
    end
    if (select COUNT([ENTITY]) from #Apple)>0 goto way;
    exec(@s)
    drop table #Apple
    drop table Snake

    And here's the result:

    Notice: this will also suit for the situation i mentioned up there(the branch one). It will output like below:

    Hope this can help.

    Best Regard,

    Yang Shen

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, September 6, 2019 6:31 AM
  • User1253338400 posted

    thanks it works great

    cheers

    Friday, September 6, 2019 10:10 AM