How to make hierarchical structure for the flat table

[masoud_sedighy]

Hello

I have 5 tables with below design

Table 1 : Docid (pk),DocNo

Table 2: Transid (pk),Transmittal No

Table 3: Docid (fk to table1),TransID(fk to table2)

Table 4: TransmittatoConID(PK), TransmittaltoCon

Table 5: Docid(fk to table 3),transID(FK to table3), TransmittatoConID(PK) “combine fields”

I have made a query from top tables that returns the below data

DocNo TransmittalNo TransmittaltoCon
Doc-0001 tt-0002 con-0005
Doc-0005 tt-0002 con-0003
Doc-0001 tt-0002 con-0007

Now I like to make a query with adding new fields (parent) , (position) that shows hierarchical structure of the fields


level 1 is (Doc No) level 2 is (Transmittal No) and level 3 is (TransmittaltoCon) ,

ID PARENT POSITION OPTION
1 0 0 DOC-0001
2 1 0 TT-0002
3 0 1 DOC-0005
4 1 1 CON-0007
5 3 0 TT-0002
6 5 0 CON-0003

[rpeare]

Don't you have a activex tree view that's doing this already?

Is there a reason you need it in a query as opposed to printing it on a report. If I understand your request you're trying to re-create your tree view in a query, is that right?

[masoud_sedighy]

Hello

yes, i like to make tree view in another way also, any way also i like to know how i can make this type of query, actually when number of records are alot (50000 records) tree view populating is a bit slow, i was thinking if i have this type of query populating may be is faster.

thanks.

[rpeare]

the quickest way to do what you want (in terms of time) is likely to be having three list boxes or combo boxes with search options rather than trying to cram all the information into a single query. The reason is pretty simple, once you pick an author, you can show only the books relevant to that author what you're asking to do is to show all books for all authors at the same time, along with all the transactions at the same time.

[masoud_sedighy]

thanks, actually i like to know how i can make the query that returns data i need with parentid and position field.

[rpeare]

The only way I can think to do this if you're determined to show all authors and all books and all transmittals in the same query is to make a union query that shows each of the portions you want, then do the sorting after the union query is showing what you want.

[masoud_sedighy]

thanks,

how i have to get parent and position values after making union query. is it possible with sample or some sql help me about values of parent and position.

[rpeare]

If you plan on using this query as a selection list, like you did the tree structure, it won't work nor will it visually look the same, your author will be repeated on each line, and for each transID for each book the book will also be repeated.

In other words if you have an author of five books, and each one of those books has 2 transID's you're going to end up with 10 records each line with the author information 2 records for each book (1 for each transID) in your list. Is that really what you want? I don't think so based on your tree view you had earlier. Because you're trying to preserve the BOOKID so when you click on it you can populate your data if you're determined to do the selection from a single list your tree view solution is likely the best one you're going to have.

I'd still recommend choosing author and book from two different lists, each list with a search function. It'll be far quicker and more efficient than what you're talking about with this union query that will not work for your intended purpose (again assuming you want something similar to the tree view in your other post).

[masoud_sedighy]

i was thinking when i have for each field parent & position value and save data of this query every 30 minutes in one table, populating tree view is more quick, because location and parent of each field in clarified so tree view should be populate more quick. for example if i have for each authorid 5 books and for each book 2 transid like below

id parent position field
1 0 0 author1
2 1 0 book1
3 1 1 book2
4 1 2 book3
5 1 3 book4
6 1 4 book5
7 2 0 trans1
8 2 1 trans2

[masoud_sedighy]

i have seen one sample that use parent and position of each field/node and it seems that is very quick i attach that.

please help how i can make like this query with parent and position value.

thanks a lot