SELECT tDocs.*, ; > mLookup.cValue AS cLevel1, ; > mLookup_A.cValue AS cLevel2, ; > mLookup_B.cValue AS cLevel3, ; > (SELECT TOP 1 SDP.iPartyID ; > FROM VSO3!sDocsParty SDP, VSO3!tDocs TD ; > WHERE SDP.iPID = TD.iID ; > ORDER BY SDP.iID) AS iPartyID ; > FROM VSO3!tDocs ; > INNER JOIN VSO3!mLookup ; > ON tDocs.ILvl1ID = mLookup.iID ; > INNER JOIN VSO3!mLookup mLookup_A ; > ON tDocs.ILvl2ID = mLookup_A.iID ; > INNER JOIN VSO3!mLookup mLookup_B ; > ON tDocs.ILvl3ID = mLookup_B.iID ; > ORDER BY mLookup.cValue, mLookup_A.cValue, mLookup_B.cValue>
SELECT TD.*, ; mLookup.cValue AS cLevel1, ; mLookup_A.cValue AS cLevel2, ; mLookup_B.cValue AS cLevel3, ; (SELECT TOP 1 iPartyID ; FROM VSO3!sDocsParty SDP ; WHERE iPID = TD.iID ; ORDER BY iID) AS iPartyID ; FROM VSO3!tDocs TD ; LEFT JOIN VSO3!mLookup ; ON TD.ILvl1ID = mLookup.iID ; LEFT JOIN VSO3!mLookup mLookup_A ; ON TD.ILvl2ID = mLookup_A.iID ; LEFT JOIN VSO3!mLookup mLookup_B ; ON TD.ILvl3ID = mLookup_B.iID ; ORDER BY mLookup.cValue, mLookup_A.cValue, mLookup_B.cValue>The following query, which will be my aim, complains that iPartyID is not found.
SELECT tDocs.*, ; > mLookup.cValue AS cLevel1, ; > mLookup_A.cValue AS cLevel2, ; > mLookup_B.cValue AS cLevel3, ; > (SELECT TOP 1 SDP.iPartyID ; > FROM VSO3!sDocsParty SDP, VSO3!tDocs TD ; > WHERE SDP.iPID = TD.iID ; > ORDER BY SDP.iID) AS iPartyID, ; > mParty.cName, ; > mParty.cCity ; > FROM VSO3!tDocs ; > INNER JOIN VSO3!mLookup ; > ON tDocs.ILvl1ID = mLookup.iID ; > INNER JOIN VSO3!mLookup mLookup_A ; > ON tDocs.ILvl2ID = mLookup_A.iID ; > INNER JOIN VSO3!mLookup mLookup_B ; > ON tDocs.ILvl3ID = mLookup_B.iID ; > INNER JOIN VSO3!mParty mParty ; > ON iPartyID = mParty.iID ; > ORDER BY mLookup.cValue, mLookup_A.cValue, mLookup_B.cValue