On Apr 18, 2006, at 11:38 AM, Trausti Thor Johannsson wrote:
I am using sqlite.
What I want is to list everything in _id order, but if there is a
connecting item, it should come below the parent item. In short, a
parent item has a 0 in connection, but a related item has the
parent _id in the connection.
If this is not possible, then I need to do my own objects for this,
but if this would be possible in one sql command, that would be
perfect.
Trausti
Oh I see .... you want to interleave them
So in this example the third row (item _id = 5 ) is a child of item
_id = 2
1 text1 0
2 text2 0
5 Sometextconn2 2
3 text3 0
4 text4 0
With a single query this is not really nice to try to do because what
you want is to order by _id only if the item is NOT a child of
another record
And this could be a recursive relationship (ie parent , child) and
this sort of thing cannot be handled in a single SQL statement unless
the DB vendor has specific extensions to the SQL language (Oracle has
such an extension for this sort of query).
I'd do this as part of walking through the results of something like
// find all the record that have no parent record
rs = db.sqlselect("select * from table where connection <> 0")
while rs.eof <> true
// now for each record find all the children
rs2 = db.sqlselect("select * from table where connection = " +
format(rs.field("connection").integerValue,"-#")
rs.movenext
wend
This only handles a single level deep.
By making the inner lookup a recursive method you could have it do as
many levels deep as you need
_______________________________________________
Unsubscribe or switch delivery mode:
<http://www.realsoftware.com/support/listmanager/>
Search the archives of this list here:
<http://support.realsoftware.com/listarchives/lists.html>
