I can't understand why SQL created with Access creates a different
(and much better) plan than what I write... it certainly is more
difficult to read

I should note that MS Access created this back when my DB was a MS SQL
Server and I did not modify the query until now... I needed to change
the query a bit and decided to rewrite it to match my usual SQL
syntax... but I cannot figure out how to make my query select the
correct plan...

I am running FB 1.5?

Access: (note, I added the linebreak to make more readable)
=======
FROM
Product INNER JOIN (
CustOrder RIGHT JOIN (
VisProdBatch INNER JOIN (
VisItem INNER JOIN VisProdParent ON VisItem.ID = VisProdParent.VisItem
) ON VisProdBatch.ID = VisProdParent.VisProdBatch
) ON CustOrder.ID = VisItem.OrderNo) ON Product.ID = VisItem.Product

PLAN JOIN (JOIN (JOIN (VISPRODBATCH NATURAL,VISPRODPARENT INDEX
(FK_VISPRODPARENT_VISPRODBATCH),VISITEM INDEX (PK_VISITEM)),CUSTORDER
INDEX (PK_CUSTORDER)),PRODUCT INDEX (PK_PRODUCT))

***Notice that CustOrder is using it's PK index... a good thing!


Now I assumed that the parens will affect the order so I went from the
interior out and created this one - but it did not create the same
plan

Mine: (attempt 1)
=======
FROM
VisItem
JOIN VisProdParent ON VisItem.ID = VisProdParent.VisItem
JOIN VisProdBatch ON VisProdBatch.ID = VisProdParent.VisProdBatch
RIGHT JOIN CustOrder ON CustOrder.ID = VisItem.OrderNo
JOIN Product ON Product.ID = VisItem.Product

PLAN JOIN (JOIN (CUSTORDER NATURAL,JOIN (VISPRODBATCH
NATURAL,VISPRODPARENT INDEX (FK_VISPRODPARENT_VISPRODBATCH),VISITEM
INDEX (PK_VISITEM))),PRODUCT INDEX (PK_PRODUCT))

***Notice that CustOrder is using a Natural scan! - yikes!


So next I added in parens in a, albeit feeble, attempt to make it work
but to no avail...

Mine: (attempt 2)
=======
FROM
((((VisItem
INNER JOIN VisProdParent ON VisItem.ID = VisProdParent.VisItem)
INNER JOIN VisProdBatch ON VisProdBatch.ID = VisProdParent.VisProdBatch)
RIGHT JOIN CustOrder ON CustOrder.ID = VisItem.OrderNo)
INNER JOIN Product ON Product.ID = VisItem.Product)

PLAN JOIN (JOIN (CUSTORDER NATURAL,JOIN (VISPRODBATCH
NATURAL,VISPRODPARENT INDEX (FK_VISPRODPARENT_VISPRODBATCH),VISITEM
INDEX (PK_VISITEM))),PRODUCT INDEX (PK_PRODUCT))

***CustOrder is still using a Natural scan!

Can someone please explain to me what I am doing wrong?

Many thanks,
Raymond

--
Raymond J. Schappe
Isthmus Technology Solutions, LLC
_______________________________________