I'm populating a tree at run time using the traditional method of nesting queries. The structure is:

Book
Chapter
Topic
Sequence

and the tree shows Books within sequence, in each book Chapters in sequence and in each chapter Topics in sequence.

I'm trying, and failing to come up with a single query that will put the table into the right order so that I can just run down it. Normally when building a tree I don't even try this because of the variable number of levels but with it fixed at three I thought it was worth a go. The problem is the sequence which is repeatable ie each chapter will have sequence 1,2,.. for its topics and each book will have 1,2.. for its chapters.

Any sql genius out there?

Roy Lambert

Roy

> I'm populating a tree at run time using the traditional method of nesting
queries. The structure is:
>
> Book
> Chapter
> Topic
> Sequence
>
> and the tree shows Books within sequence, in each book Chapters in
sequence and in each chapter Topics in sequence.
>
> I'm trying, and failing to come up with a single query that will put the
table into the right order so that I can just run down it. Normally when
building a tree I don't even try this because of the variable number of
levels but with it fixed at three I thought it was worth a go. The problem
is the sequence which is repeatable ie each chapter will have sequence
1,2,.. for its topics and each book will have 1,2.. for its chapters.

I'm probably being thick but can you give some example data and how it
should be ordered?

John

John

Its actually going to be for a help system so roughly Books will be the various programs that make up the system, Chapters the forms within each .exe and topics the components so you might get something like in raw order in the table


TfR, null, null, 1
TfR, ContactForm, forename, 1
TfR, MainForm, null, 1
Utilities, ConfigMaint, null, 3
TfR, MainForm, PerformanceGrid, 12
TfR, ContactForm, Role, 9
TfR, ContactForm, null, 4
TfR, ContactForm, surname, 2
Utilities, null, null, 4

Traditionally you'd use nested queries or filters eg to get the chapters - SELECT * FROM Manual WHERE Book = 'TfR AND Topic IS NULL ORDER BY Sequence

Since I know there will only ever be three levels what I'm wanting is

TfR, null, null, 1
TfR, MainForm, null, 1
TfR, MainForm, PerformanceGrid, 12
TfR, ContactForm, null, 4
TfR, ContactForm, forename, 1
TfR, ContactForm, surname, 2
TfR, ContactForm, Role, 9
Utilities, null, null, 4
Utilities, ConfigMaint, null, 3

If I can do that its one query and just run through it adding to the tree rather than loads of queries. It may end up being no faster but I'd like to try it if I can get the query into the right sequence.

Roy Lambert

Roy,

Well the following should work (it doesnt .  The coalesce is not needed
if nulls are guaranteed to be sorted first.

select book,
(select sequence from books b1 where books.book=b1.book and b1.chapter is
null and b1.topic is null) as booksequence,
chapter,topic,
coalesce((select sequence from books b1 where books.book=b1.book and
b1.chapter=books.chapter and b1.topic is null),-1) as chaptersequence,
sequence
from books
order by booksequence,chaptersequence,sequence

There seems to be a serious problem with correlated subqueries in 2.02 build
14. For example total in the following query (from your data) is the same
for all records (the correlation part appears to be broken).

select book,
(select sum(sequence) from books b1 where b1.book=books.book) as total
from books

Cheers

John

John


I think I can see what you're trying to do but I'm not sure - way beyond me - maybe Tim will understand and fix whatever it is that's wrong.

Roy Lambert

John


I think I've got my head round your sql and had a mess about in SQLite which is where it will end up and got it working

select _Book,_chapter,_topic,
(select _sequence from manual b1 where b1._book=manual._book and b1._chapter = '' and b1._topic ='') as booksequence,
(select _sequence from manual b2 where b2._book=manual._book and b2._chapter=manual._chapter and b2._topic ='') as chaptersequence,
_sequence
from manual
order by booksequence,chaptersequence,_sequence

Now to test out if its any faster in populating the tree. Thanks


Roy Lambert

John


Just for interest, and don't know about ElevateDB but in SQLite the query takes c1.5 seconds to run and the traditional way with nested queries takes c.1 seconds to run and populate the grid. Its been an interesting exercise, stretched my knowledge and show that at least for one db engine a number of simple queries runs a lot faster than a big complex one.

Roy Lambert

ps Tim I'm not being too disloyal SQLite is only going to be used for the OLH stuff - small and single file and I don't have to worry about multiple TEDBEngines clashing with each other

Roy

> Just for interest, and don't know about ElevateDB but in SQLite the query
takes c1.5 seconds to run and the traditional way with nested queries takes
c.1 seconds to run and populate the grid. Its been an interesting exercise,
stretched my knowledge and show that at least for one db engine a number of
simple queries runs a lot faster than a big complex one.

Interesting.  I guess ElevateDB would be much the same.

Just for interest (I know I must get out more if all books have at
least one chapter and all chapters have at least one topic how does the
following perform (and does it produce the right data!)

select manual._book,manual._chapter,manual._topic,
manual._sequence,m1._sequence as chaptersequence,m2._sequence as
booksequence from manual
join manual m1 on manual._book=m1._book and manual._chapter=m1._chapter
join manual m2 on m1._book=m2._book and m2._chapter is null and m2._topic is
null
where manual._chapter is not null and manual._topic is not null and
m1._topic is null
order by m2._sequence,m1._sequence,_sequence

John

John,

<< There seems to be a serious problem with correlated subqueries in 2.02
build 14. For example total in the following query (from your data) is the
same for all records (the correlation part appears to be broken). >>

Could you send me the data that you're using for this ?  Also, do you have
RequestSensitive set to True or False ?

--
Tim Young
Elevate Software
www.elevatesoft.com

John


Nope. When I alter the IS NULL to = '' (I decided to have a default) I get only the books.

Roy Lambert