> this query takes a couple seconds with dicentries containing some
> 100000 entries!

Which doesn't mean it couldn't run much faster if you used a JOIN :-)

Let alone being it much more clear to read.

Martijn Tonies
Database Workbench - development tool for Firebird and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

> --- In firebird-support@yahoogroups.com, Helen Borrie <helebor@...> wrote:
> >
> > At 07:32 PM 17/05/2006, you wrote:
> > >hello
> > >i have a very rudimentary knowledge of sql as it's only one part of
> > >the project im in right now and in order to find records in my
> > >database i have my delphi application generate sometimes very awkward
> > >queries and like for example
> > >
> > >select * from (select * from (select * from (select * from (select *
> > >from dicentries where id in (select id_dicentry from
> > >dskrptlink_dicentries where id_dskrpt = (select id from dskrpts where
> > >asdskrpt = 'ios'))) where id in (select id_dicentry from
> > >dskrptlink_dicentries where id_dskrpt = (select id from dskrpts where
> > >asdskrpt = 'iop'))) where id in (select id_dicentry from
> > >dskrptlink_dicentries where id_dskrpt = (select id from dskrpts where
> > >asdskrpt = 'ois'))) where id < 2067 order by id descending rows 20)
> > >order by id ascending
> > >
> > >(for some clarity: dskrptlink_dicentries.id_dicentry = foreign key on
> > >dicentries.id; dskrptlink_dicentries.id_dskrpt = foreign key on
> > >dskrpts.id => dskrptlink_dicentries is table establishing a
> > >many-to-many link between tables dicentries and dskrpts...so this
> > >query would ask for the last 20 of all those records in dicentries
> > >that are linked via dskrptlink_dicentries to ALL three records in
> > >dskrpts where dskrpts.asdskrpt = 'ois', asdskrpt = 'iop', asdskrpt =
> > >'ios' and then order them ascendingly)
> > >
> > >i'm pretty sure if i had better knowledge of sql there would be an
> > >easier way to express the same thing but my question is: is it worth
> > >it? will it make my searches faster or can i assume that firebird
> > >always looks for the fastest way possible?
> >
> > Your question comes into the category "How long is a piece of
> > string?" If this query actually runs at all (doubtful) it will
> > probably take a week or two to complete.
> >
> > Is it worth understanding the SQL language to use Firebird? Yes.
> > (understatement!!).
> >
> > I think I might add your posting to my forthcoming book, "Essential
> > SQL for Skateboard Riders". :-)
> >
> > Get your best SQL textbook and look up the keyword JOIN. If you
> > don't own an SQL textbook and you have a birthday coming soon, put it
> > at the top of your wish-list. If you attend an IT school, visit the
> library.
> >
> > ./heLen