Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Parent Child Relationships

Author  Topic 

filf
Yak Posting Veteran

67 Posts

Posted - 2006-08-02 : 08:51:36
Hello.

I have created two tables

T1: contains record data for galleries.
contains: [int_gallery_id, int_display_order, misc fields]
T21: contains xref data for these galleries linking a child to a parent.
contains: [int_gallery_id, int_parent_id, int_depth]

Each gallery relates to a folder in a filesystem which are nested based on parent child relationships. I have only have two depths of folders currently but would possibly like to extend this to be flexible.

A user can change the display order of the parents and then subsequently the children.

My problem is the correct ORDER BY sql to order the galleries on select - based on display_order of the parent, then the display_order of the children, then the display_order of the next parent etc etc.

I hope I am making sense - my xref table funnily enough looks much like the table in this example without the lineage.
http://www.sqlteam.com/item.asp?ItemID=8866

Thanks in advance.

filf
Yak Posting Veteran

67 Posts

Posted - 2006-08-02 : 11:57:01
Okay - I have not been very clear - I have been reading the trees and hierarchies articles but what I am trying to do is slightly different.

I have two tables - gallery and gallery_xref
and a select which is joining them.
SELECT t1.int_gallery_id , t2.int_parent_gallery_id, t1.int_display_order, t2.int_gallery_depth
FROM gallery AS t1 INNER JOIN gallery_xref t2 ON t1.int_gallery_id = t2.int_gallery_id
ORDER BY ??????

The data is being displayed as follows
[t1.int_gallery_id], [t2.int_parent_gallery_id], [t1.int_display_order], [t2.int_gallery_depth]
1, 1, 1, 1
2, 1, 1, 2
3, 1, 2, 2
4, 4, 2, 1
5, 5, 3, 1
6, 5, 1, 2

NOTE: if a gallery is level 1 then the parent id is itself. This was an attempt at fixing the issue simply with order by.

My problem is how to create the sql to order the returned rows based on t1.int_display_order as specified by a user. So if in the sample data they wanted gallery 5 to be displayed first then the user would be displayed first gallery5 then all of gallery 5's children, then the next level 1 gallery as specified by int_display_order.

I am sure I have over complicated this and there is a simple solution staring me in the face - any help will be greatly appreciated.

Thanks for looking.
Go to Top of Page

filf
Yak Posting Veteran

67 Posts

Posted - 2006-08-02 : 13:04:14
Okay here is an angle which needs more work.

I made a temp table and copied some data across into the following fields.
[int_gallery_id], [int_portfolio_id], [int_parent_gallery_id], [str_gallery_name], [int_display_order], [int_child_display_order]
65, 3, 65, [Parent 1], 1, 0
68, 3, 65, [child 1-2], 1, 1
66, 3, 65, [child 1-1], 1, 2
69, 3, 69, [Parent 3], 2, 0
71, 3, 69, [child 3-1], 2, 1
72, 3, 69, [child 3-2], 2, 2
70, 3, 70, [parent 4], 3, 0
67, 3, 67, [parent 2], 4, 0
73, 3, 67, [child 2-1], 4, 1

I will look further into my xref table and add an additional field [parent_display_order] which can then be the field to order my results by in conjunction with the original display order.

I will TEST and post my results - this should then work for all depths which are not displayed here.
Go to Top of Page

filf
Yak Posting Veteran

67 Posts

Posted - 2006-08-06 : 07:31:11
Thought I should do my bit for the dev community and complete posts -

My working solution is based on what the article mentioned above was talking about - the final table def with example data is as follows:

[int_node] [int_parent_node] [int_gallery_id] [int_portfolio_i] [int_gallery_depth]
100, 100, 6, 1, 1
101, 101, 8, 1, 1
102, 101, 9, 1, 2
103, 103, 11, 1, 1

The key to getting the data out in the order specified by the user is the query which is as follows:
SELECT t1.int_gallery_id, t2.int_node, t1.str_gallery_name, t1.str_gallery_folder, t1.int_total_images, t1.int_max_images, t1.int_display_order as int_display_order, t1.bln_displayed as bln_displayed, t2.int_gallery_depth, t3.int_gallery_id AS int_parent_gallery_id, t4.int_display_order AS int_parent_display_order, FROM gallery t1
INNER JOIN gallery_xref t2 ON t1.int_gallery_id = t2.int_gallery_id
INNER JOIN gallery_xref t3 ON t3.int_node = t2.int_parent_node
INNER JOIN gallery t4 ON t4.int_gallery_id = t3.int_gallery_id
WHERE t1.int_portfolio_id = 3 AND t1.bln_displayed = 1
ORDER BY t4.int_display_order, int_parent_gallery_id, int_gallery_depth, int_display_order';

What this enables is the functionality I was after - for results to be ordered by parent order - but if a parent has children its children are displayed based on their order, then the next parent is displayed and any subsequent children - etc etc.

I have not tried any deeper than one level of children with this code but - after a couple of other tweaks I will experiment.

Don't know why I recieved no suggestions or help on this topic - was I not clear enough in terms of the desired functional outcome?


Go to Top of Page

filf
Yak Posting Veteran

67 Posts

Posted - 2006-08-06 : 14:27:41
BAH - does not work on deeper levels of ordering - back to the drawing board - looking more closely at lineage now.
Go to Top of Page
   

- Advertisement -