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