Joining two tables to modify a tree structure

I’m trying to create a modified catalog tree out from two tables in sql

The catalog is created from two tables.

Table 1 has the lowest level and is showing the connection with the item and the lowest ItemGroup. It also shows the connection with the MainCatalog

MainCatalogId, ItemGroupId, ItemId

Example data:

(sorry about the tabs, can't get them right here)
MainCatalogIdItemGroupIdItemId
715063823
715073824
715093825
715093826

The catalog structure is in table 2. Here are the connections between the different

ItemGroupId, ParentId

Example data:

ItemGroupIdParentId
15061365
15071365
15091364
13641066
13651066
1066NULL

To be able to use create the tree structure and not getting the result set to big, I need it to look something like this:

ItemGroupIdItemIdLevel 2 level 3Level4
15063823136510667
15073824136510667
15093825136410667
15093826136410667

I have tried many ways, but I’m not getting the result I want.

Hope this was understandable, and that someone has an idea if this is manageable...

Thanks :)

Quote:

Originally Posted by KingKong07

Im trying to create a modified catalog tree out from two tables in sql

The catalog is created from two tables.

Table 1 has the lowest level and is showing the connection with the item and the lowest ItemGroup. It also shows the connection with the MainCatalog

MainCatalogId, ItemGroupId, ItemId

Example data:

(sorry about the tabs, can't get them right here)
MainCatalogIdItemGroupIdItemId
715063823
715073824
715093825
715093826

The catalog structure is in table 2. Here are the connections between the different

ItemGroupId, ParentId

Example data:

ItemGroupIdParentId
15061365
15071365
15091364
13641066
13651066
1066NULL

To be able to use create the tree structure and not getting the result set to big, I need it to look something like this:

ItemGroupIdItemIdLevel 2 level 3Level4
15063823136510667
15073824136510667
15093825136410667
15093826136410667

I have tried many ways, but Im not getting the result I want.

Hope this was understandable, and that someone has an idea if this is manageable...

Thanks :)

I think you have to Innerjoin the tables

Just go through this|||Thanks for answer, I had made it a bit to complex.

Worked it out.

Used inner join to get the first data, and run an update afterwards to update level 3 and 4|||

Quote:

Originally Posted by KingKong07

Im trying to create a modified catalog tree out from two tables in sql

The catalog is created from two tables.

Table 1 has the lowest level and is showing the connection with the item and the lowest ItemGroup. It also shows the connection with the MainCatalog

MainCatalogId, ItemGroupId, ItemId

Example data:

(sorry about the tabs, can't get them right here)
MainCatalogIdItemGroupIdItemId
715063823
715073824
715093825
715093826

The catalog structure is in table 2. Here are the connections between the different

ItemGroupId, ParentId

Example data:

ItemGroupIdParentId
15061365
15071365
15091364
13641066
13651066
1066NULL

To be able to use create the tree structure and not getting the result set to big, I need it to look something like this:

ItemGroupIdItemIdLevel 2 level 3Level4
15063823136510667
15073824136510667
15093825136410667
15093826136410667

I have tried many ways, but Im not getting the result I want.

Hope this was understandable, and that someone has an idea if this is manageable...

Thanks :)

1- i think you should have a MainCatalogId in your second table.because if you have many Maincatalog you can not save relation for other Maincatalog if you have repeated code of items.
2-you must create a dataset returning storedprocedure with the columns for your output.
3-in your storedprocedure you must have three cursor with three loop for three level(suppose you have three level),each loop contains parent loop.then fetch what you want to output for your output dataset.
4-use your storedprocedure As a table in your application .