It may be easier if I post the SQL:

SELECT
rplgrp.ID, vcpi.RXCLAIMNBR, vcpi.CLMSEQNBR, vcpi.CLAIMSTS, vcpi.NDC,
vcpi.DECIMALQTY, vcpi.DATESBM, vcpi.QTY_CLAIM_ORDER,
vcpi.QTY_CLAIM_SHIP,
vcpi.QTY_CLAIM_BOUGHT, vcpi.LAST_PO_DATE
FROM
V_CLAIM_PO_INVOICE vcpi
JOIN CHC chc ON vcpi.CHC = chc.CHC
JOIN GRANTEE g ON chc.GRANTEE = g.COMPANYID
JOIN GRANTEE_REPLENISH_GRP rplgrp ON g.COMPANYID = rplgrp.GRANTEE
JOIN PHARM_GRANT_GRP phgr ON phgr.REPLENISH_GRP = rplgrp.ID
JOIN PHARMACY ph ON ph.NABPNUM = phgr.PHARMACY
WHERE
vcpi.PHARMACY = ph.NABPNUM

or

SELECT
rplgrp.ID, vcpi.RXCLAIMNBR, vcpi.CLMSEQNBR, vcpi.CLAIMSTS, vcpi.NDC,
vcpi.DECIMALQTY, vcpi.DATESBM, vcpi.QTY_CLAIM_ORDER,
vcpi.QTY_CLAIM_SHIP,
vcpi.QTY_CLAIM_BOUGHT, vcpi.LAST_PO_DATE
FROM
V_CLAIM_PO_INVOICE vcpi
JOIN CHC chc ON vcpi.CHC = chc.CHC
JOIN GRANTEE g ON chc.GRANTEE = g.COMPANYID
JOIN GRANTEE_REPLENISH_GRP rplgrp ON g.COMPANYID = rplgrp.GRANTEE
JOIN PHARM_GRANT_GRP phgr ON phgr.REPLENISH_GRP = rplgrp.ID
JOIN PHARMACY ph ON ph.NABPNUM = phgr.PHARMACY AND ph.NABPNUM =
vcpi.PHARMACY

The first completes the join using a where clause, so it seems
SQL89-ish. The latter doesn't use a where clause, but the AND in the
final join doesn't seem right (probably due to my lack of knowledge).
And for some reason, the first version runs slightly quicker even though
they both have the same plan.

-----Original Message-----
From: Rick DeBay
Sent: Wednesday, January 19, 2005 1:58 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Circular join question


I have a circular relationship:

Top_Entity1 <-- Entity_Group <-- Entity1_Entity2_Link
^ |
| |
| V
Child <-- Child_Entity2_Link <-- Top_Entity2

To get the information I need, there are at least five joins.
I don't know if I should add a sixth join to close the circle, or a
where clause to do it.
For example, given that all other parts of the SQL consist of joins,
should I finish with

JOIN Child_Entity2_Link a ON a.Top_Entity2 = Top_Entity2.ID

or

WHERE Child_Entity2_Link.Top_Entity2 = Top_Entity2.ID

I ask because Child_Entity2_Link is what started the join statements,
with

FROM Child_Entity2_Link JOIN Top_Entity1 ON ...

and I don't know if it is correct to have the join closed like that, or
a burden on the engine.

Rick DeBay
Senior Software Developer
RxStrategies.net



Yahoo! Groups Links