Roy

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

Either there is something very strange with SQLite or you have changed IS
NOT NULL to = '' instead of <> ''.

Cheers

John

John


OK Sherlock you were right. Now I get all the topics and in the right order but not the chapter or book.

Roy Lambert

Roy

> OK Sherlock you were right. Now I get all the topics and in the right
order but not the chapter or book.

LOL

Do you mean the books and chapters are not in the right order?  If not the
book and chapter info are right there in the result set providing there is
at least one topic ... etc.

Cheers

John

John


No I mean that everything is in the right order just that books (ie chapter = '' and topic = '') and chapters (topic = '') aren't in the result set. And now I understand your comment

<<if all books have at least one chapter and all chapters have at least one topic>>

if phrased as a question the answer would be yes - but not in the database The more normal approach would be to have a separate table for books and a separate table for chapters and a separate table for topics together with appropriate link tables.

Roy Lambert

Roy

>The more normal approach would be to have a separate table for books and a
separate table for chapters and a separate table for topics >together with
appropriate link tables.

Far too sensible

I was really interested in whether the joins were faster than the correlated
subquery.  One last trying using joins, union and derived tables

select * from
(select book,chapter,topic,sequence,-1 as chapterseq,-1 as topicseq from
manual where chapter = '' and topic = ''
union all
select manual.book,manual.chapter,manual.topic,m1.sequence,manual.sequence
as chapterseq,-1 as topicseq from manual
join manual m1 on m1.book=manual.book
where manual.topic = '' and m1.topic = '' and m1.chapter = '' and
manual.chapter <> ''
union all
select manual.book,manual.chapter,manual.topic
,m2.sequence,m1.sequence as chapterseq,manual.sequence as topicseq 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 = '' and m2.topic = ''
where manual.chapter <> '' and manual.topic <> '' and
m1.topic = '') t1
order by t1.sequence,t1.chapterseq,t1.topicseq

John

John


You're right - you must get out more <vbg>

That version runs about twice the speed of the ones with subselects taking c980 ticks, produces the right results but is still a lot slower than using nested queries.

I must say that I'm incredibly impressed that you could come up with a query to produce the results regardless of speed and you've managed it twice!

Roy Lambert

The issue is with correlated sub-queries that reference the same table as
the outer query.  A fix will be in the next build.

--
Tim Young
Elevate Software
www.elevatesoft.com