I have 5 tables that store records for 5 different types of documents. Each type of document can be a descendent of a document in one of the other tables.
For example, let's call the tables A, B, C, D and E. If you create a record in A, and then create a record in B, you can associate the document in B with the record in A. You could then do the same with a record in C, D, and E. One wrinkle is that you can skip documents, so it's not always a direct chain. For example, you could have A > B > E. Another wrinkle is that you don't have to start at A, so you could have B > C > D > E. You can actually start at any point in the cycle. Another wrinkle is that you can loop back around to other tables again, so something like A > B > E > A (not the same record as the first A) > C > E is possible. Of course, you can't link back to a record that is already in the chain though, so as to avoid and infinite loop. A final wrinkle is that each parent record can have more than one linked child in other tables. Each table has 2 columns: ParentTable and ParentRecordID that hold the associated record from the immediate parent table for each child record (or null for records that are at the top of a chain, or completely unchained at this point). Now Table A is kind of special in that I want to be able to figure out the deepest descendents of all of the records in Table A in any other tables (if any) and display data from the deepest linked records (using group_concat on a fields in those linked records). So, for example, if we had record chains such as: D1 < B1 < A1 D2 < C1 < A3 E1 < B3 < A1 E2 < C2 < A1 C3 < B2 I want to be able to show a cell for each record in Table A like: TABLE A A.ID A.Title DeepestDocuments ------------------------------------------ 1 Doc1 E.Doc1, E.Doc2 2 Doc2 Null 3 Doc3 D.Doc2 You'll notice that even though A1 has descendents in tables B, C and D, only descendents in Table E are listed in the DeepestDocuments cell because that is the deepest table with descendents. I've got what I need to work by manually looping through records and running up the chain to see when I get a null parent record to determine the top level reference, and then concatenating all of the deepest records fields as required, but it is quite slow. On 100 rows it can take 3-6 seconds on a reasonably fast computer, but table A could conceivably contain a few thousand records, which would make it far too slow for my needs. I'm wondering if anyone has any ideas on how I might accomplish what I need using SQLite supported SQL. I suspect I need to do something with UNION statements, but my experience with them is non-existent, and nothing I've tried yet has worked. Thanks in advance for any help in this matter. Jason _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
