September 19, 2012 at 5:36 am
Hi,
I wonder if someone could please help me on a self joining query.
I have a relationship table, that has two main columns, column1 being the start of the tree and the second column2 holding the child of the parent (column1).
So the table looks like as follows:
column1 column2
1 2
1 3
1 4
5 6
5 7
5 8
However I would like to write a query that can give me the output of this table in 3 columns, which would output them in side by side order, to show something as follows:
column1 column2 column3
NULL NULL 1
NULL 1 2
1 2 3
2 3 4
NULL NULL 5
NULL 5 6
5 6 7
7 8 NULL
I can output the first two columns with the following query but I can not seem to get a third column.
select pp1.K_PROJECT as ParentID, isnull(pp2.K_PROJECT2, pp1.K_PROJECT) as CurrentID, pp1.K_PROJECT2 as SecondID
into #cteRelation
from adminmlex.PROJ_PROJ pp1
outer apply ( select top 1 K_PROJECT2
from adminmlex.PROJ_PROJ
where K_PROJECT = pp1.K_PROJECT
and (K_PROJECT2 < pp1.K_PROJECT2)
order by K_PROJECT2 desc ) pp2
Please could someone help me get the third column output?
Any help would be greatly appreciated.
Thank you in advance.
Regards,
Lewis
September 19, 2012 at 6:18 am
What do the three columns represent?
I think there are some logic errors in your desired output because the results do no appear to be consistent (which makes it difficult to write the query)
What are you trying to do with the results set: I am guessing you are building a menu tree... There may be better ways to approach the problem
September 19, 2012 at 6:23 am
Sorry the format is a little confusing, the three columns represent the order of the tree.
Please use code below to get a better representation...
create table #original (K_PROJECT int, K_PROJECT2 int)
insert into #original select 1, 2
insert into #original select 1, 3
insert into #original select 1, 4
insert into #original select 5, 6
insert into #original select 5, 7
insert into #original select 5, 8
select * from #original
create table #new (a int, b int, c int)
insert into #new select NULL, NULL, 1
insert into #new select NULL, 1, 2
insert into #new select 1, 2, 3
insert into #new select 2, 3, 4
insert into #new select NULL, NULL, 5
insert into #new select NULL, 5, 6
insert into #new select 5, 6, 7
insert into #new select 6, 7, 8
select * from #new
drop table #original
drop table #new
September 19, 2012 at 6:30 am
does your tree look like this
1
--2
--3
--4
5
--6
--7
--8
That is the structure defined in your #Original table.
the data in #New doesn't mnake sense, because the top level and submenus are mixed up in different columns.
What are you going to do with the output
if your menu looks like this
1
--2
----3
--4
5
--6
----7
----8
Then that relationship is not reflected in the #Original table
September 19, 2012 at 7:34 am
Effectively the first column holds the parent id, i.e. the first node in the hireachy. Column 2 holds the child, each subsequent child is a child of the previous child.
The output I require is a the three columns in the order presented in the original table, but in order of child ids rather than original parent followed by child ids.
Perhaps the following is less confusing?
create table #original (K_PROJECT int, K_PROJECT2 int)
insert into #original select 1, 2
insert into #original select 1, 3
insert into #original select 1, 4
insert into #original select 5, 6
insert into #original select 5, 7
insert into #original select 5, 8
select * from #original
create table #new (a int, b int, c int)
insert into #new select 1, 2, 3
insert into #new select 2, 3, 4
insert into #new select 5, 6, 7
insert into #new select 6, 7, 8
select * from #new
drop table #original
drop table #new
September 19, 2012 at 9:30 am
It doesn't matter how many times you post the #new DML, it still won't be right :w00t:
this is the tree structure in your #Original table
1
--2
--3
--4
5
--6
--7
--8
Each sub menu is one level deep from one of the parent menus (1 or 5)
This (I think) is what you are trying to describe with #New
1
--2
----3
------4
5
--6
----7
------8
Each sub menu is a sub menu of the previous menu. Leaving aside the fact that this is probably bad design, Can you confirm which tree structure you are trying to generate, or if neither of them are correct, can you draw it like I have. (use the [ code ] tags
September 19, 2012 at 9:45 am
OK,
So as you described below is correct, that's the original 🙂
So
1
--2
--3
--4
5
--6
--7
--8
To put it in to perspective:
Each id in the table above represents a Subscription to a annual service that we sell access to. Where 1 and 5 are the first subscriptions made for a given client in our system. So 1 was the first subscription for client X and subscriptions 2, 3, 4 were the subsequent subscriptions sold after the original subscription 1 i.e. one after the other - for each year.
It is safe to say that subscription 2 came before 3 and 3 came before 4, etc because the latest subscription id will always be greater than the previous subscription.
What I am trying to get to is a format with 3 columns: Previous Subscription | Current Subscription | Next Subscription
So using the relation ship between 1-2 | 1-3 | 1-4
I would like to see the relationship as chronological in the 3 column format:
NULL - NULL - 1
NULL - 1 - 2
1 - 2 - 3
2 - 3 - 4
3 - 4 - NULL
4 - NULL - NULL
Once I have the format above I can determine that for example:
The current subscription is 3 and the previous subscription was 2 and the next subscription will be 4 based on the row (there are other columns I will use for this filtering):
2 - 3 - 4
It must be possible to derive the above since we know that 2,3,4 are all related to 1 and that 4 > 3 and that 3 > 2
Does the above make sense?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply