oops, now i have another probleme

Select
First 10
MAINTABLE.ID
from
MAINTABLE
where
(MAINTABLE.name = 'jean')
ORDER BY MAINTABLE.DATE

UNION

Select
First 10
SECONDTABLE.ID
from
SECONDTABLE
where
(SECONDTABLE.name = 'jean')
Order by SECONDTABLE.DATE


gave me an error :(

--- In firebird-support@yahoogroups.com, Svein Erling Tysvær <svein.erling.tysvaer@...> wrote:
>
> >Hello,
> >
> >this is my query :
> >
> >Select
> > MAINTABLE.ID
> >from
> > MAINTABLE
> >Join SECONDTABLE on SECONDTABLE.ID_obj=MAINTABLE.ID_obj
> >Join THIRDTABLE on THIRDTABLE.ID_obj=MAINTABLE.ID_obj
> >where
> > (MAINTABLE.name = 'jean' or
> > SECONDTABLE.name = 'jean' or
> > THIRDTABLE.name = 'jean')
> >
> >I have an index on
> >
> >MAINTABLE.name
> >SECONDTABLE.name
> >THIRDTABLE.name
> >
> >the probleme is that firebird use
> >
> >PLAN JOIN (THIRDTABLE NATURAL, SECONDTABLE INDEX (RDB$PRIMARY436), MAINTABLE INDEX (RDB$PRIMARY26))
> >
> >and it not use the index of the field
> >MAINTABLE.name
> >SECONDTABLE.name
> >THIRDTABLE.name
>
> Sorry, that's not possible. Logically, it has to start with one of the tables with no knowledge about the others and applying that index would only find, say, 1/3 of the cases. Moreover, unless ID_OBJ is unique, your query might not return what you want in all cases. If SecondTable contained two 'Jean's and ThirdTable three 'Jean's for the same ID_obj, that will give you at least six rows with the same MAINTABLE.ID. What you probably want is a simple UNION rather than OR:
>
> SELECT ID
> FROM MAINTABLE
> WHERE name = 'jean'
> UNION
> SELECT MT.ID
> FROM MAINTABLE MT
> JOIN SECONDTABLE ST ON MT.ID_obj=ST.ID_obj
> WHERE ST.name = 'jean'
> SELECT MT.ID
> FROM MAINTABLE MT
> JOIN THIRDTABLE TT on MT.ID_obj=TT.ID_obj
> WHERE name = 'jean'
>
> (add ALL to each UNION if you want to include duplicates)
>
> If things are more complex, you might consider using one or more CTEs (though it isn't really useful in this simple example):
>
> WITH MyCTE(ID_OBJ) AS
> (SELECT ID_OBJ
> FROM MAINTABLE
> WHERE name = 'jean'
> UNION
> SELECT ID_OBJ
> FROM SECONDTABLE
> WHERE name = 'jean'
> SELECT ID_OBJ
> FROM THIRDTABLE
> WHERE name = 'jean')
>
> SELECT M.ID
> FROM MAINTABLE M
> JOIN MyCTE CTE ON M.ID_OBJ = CTE.ID_OBJ
>
> HTH,
> Set
>