Thank you Set, I will try your suggestion and see what the results
are. If I found anything that's interesting, I will post it at
ib_support group. I apologise for making this question here.

regards,
Kenneth


--- In IBObjects@yahoogroups.com, Svein Erling Tysvaer
<svein.erling.tysvaer@k...> wrote:

> Hi again Kenneth!
>
> >Any Idea?
>
> Well, actually I've got two. The simplest is just to remove RIGHT

from your

> query (you haven't convinced me that a right join is what you

want), i.e.

>
> SELECT H.REFNO
> , A.NAME
> , A.STROKE
> , A.S_CODE
> , H.DOSAGE
> , H.CHARACTERISTIC
> , H.SYSTEM
> , H.FUNC
> , H.USES
> , H.REMARK
> FROM ALL_HERBAL A
> JOIN HERBAL_WITH_CAT HC
> ON HC.H_REFNO = A.REFNO
> JOIN HERBAL H
> ON H.REFNO = A.REFNO
> WHERE HC.HC_REFNO in (2, 24, 25, 26, 33, 34, 36, 37);
>
> The other is a spinoff of your second attempt:
>
> SELECT
> ...
> FROM ALL_HERBAL A
> JOIN HERBAL H ON A.REFNO=H.REFNO
> AND EXISTS(SELECT 1 FROM HERBAL_WITH_CAT HC WHERE A.REFNO =

HC.H_REFNO AND

> HC.HC_REFNO IN (2, 24, 25, 26, 33, 34, 36, 37))
>
> Both of them ought to be pretty quick, though the result may be

slightly

> different if H_REFNO is not unique within HERBAL_WITH_CAT (I

suspect the

> first query in such a case would return duplicates).
>
> I think it is time to move this discussion over to
> ib-support@yahoogroups.com if your next reply is anything more

than

> "thanks, it works" (most of us here watch ib-support as well, I

think, but

> ib-support have some additional people that do not use IBO).
>
> HTH,
> Set