Perfect? Well, then, if gttItemsSelected.ID is unique, I would recommend changing to

WITH RECURSIVE Hier(iID, iPK) AS
(SELECT p.iID, p.iID AS iPK FROM mItems p
JOIN gttItemsSelected gis on p.iID = gis.ID
WHERE TRIM(gis.JobID) = '_2M60RJ9T4'
UNION ALL
SELECT H.iID, p2.iID AS iPK FROM mItems p2
JOIN Hier H on p2.iPID = H.iPK)

SELECT Hier.iID AS iItemID, Hier.iPK AS iItemPK, sGRNItem.iID
FROM sGoodsRecvNoteItem sGRNItem
JOIN Hier
ON sGRNItem.iItemID = Hier.iPK

If gttItemsSelected.ID is not unique, one of your options would be

WITH RECURSIVE gisTemp(ID) AS
(SELECT DISTINCT g.ID
FROM gttItemsSelected g
WHERE TRIM(g.JobID) = '_2M60RJ9T4'),

Hier(iID, iPK) AS
(SELECT p.iID, p.iID AS iPK FROM mItems p
JOIN gisTemp gis on p.iID = gis.ID
UNION ALL
SELECT H.iID, p2.iID AS iPK FROM mItems p2
JOIN Hier H on p2.iPID = H.iPK)

SELECT Hier.iID AS iItemID, Hier.iPK AS iItemPK, sGRNItem.iID
FROM sGoodsRecvNoteItem sGRNItem
JOIN Hier
ON sGRNItem.iItemID = Hier.iPK

If your tables are small, there's not much difference between this and your statement, but if mItems is considerably larger than gttItemsSelected or you use an expression index for TRIM(gttItemsSelected.JobID), you should notice the difference (the above select can use a plan that starts with finding all records with TRIM(gttItemsSelected.JobID) = '_2M60RJ9T4' (unfortunately, unless you use an expression index, I don't think Firebird can use an index with TRIM) and then look up those records in mItems that have a matching iID, whereas using IN ... probably means that the optimizer will scan through all records in mItems and only use index(es) for gttItemsSelected).

Now I've digressed into fine tuning your statement, if I'd noticed that you used TRIM, I'd probably not even started writing this email.

HTH,
Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Bhavbhuti Nathwani
Sent: 29. mars 2009 20:05
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: Self-Joined Items Master and transactions stock ledger

Hi Svein

There is no need to say sorry, I appreciate your help to me immensely and I can understand you have other commitments too, but I am totally obsessed with getting this thing perfect :).

Yessss!!! Thanks for the latest update to the query (see the commented line about something I learnt, sorry I am a slow learner)

WITH RECURSIVE Hier(iID, iPK) AS
(SELECT p.iID, p.iID AS iPK FROM mItems p WHERE p.iID IN
(SELECT ID
FROM gttItemsSelected
WHERE TRIM(JobID) = '_2M60RJ9T4')
UNION ALL
SELECT H.iID, p2.iID AS iPK FROM mItems p2
JOIN Hier H on p2.iPID = H.iPK)

--SELECT * from Hier
SELECT Hier.iID AS iItemID, Hier.iPK AS iItemPK, sGRNItem.iID
FROM sGoodsRecvNoteItem sGRNItem
JOIN Hier
ON sGRNItem.iItemID = Hier.iPK

I will now go ahead with the actual coding and will keep you posted.

Thanks and regards.
Bhavbhuti