Friday, March 23, 2012

Joining two tables to modify a tree structure with more information

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 for the tabs that won't work)
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
1506 3823136510667
1507 3824136510667
1509 3825136410667
1509 3826136410667
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 :)Worked it out.Used inner join to get the first data, and run an update afterwards to update level 3 and 4sql

No comments:

Post a Comment