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
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:
Member
231 Points
905 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
Participant
1760 Points
570 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:
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
Member
231 Points
905 Posts
Re: How can i do a self join on a table ?
Sep 06, 2019 10:10 AM|robby32|LINK
thanks it works great
cheers