How can i do a self join on a table ?

Reply

robby32

Member

268 Points

985 Posts

How can i do a self join on a table ?

Sep 06, 2019 05:04 AM|robby32|LINK

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

Reply

Yang Shen

Contributor

3100 Points

983 Posts

Re: How can i do a self join on a table ?

Sep 06, 2019 06:31 AM|Yang Shen|LINK

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: