Приветствую. Проблема с созданием Join'а.
Имеется вот такой SelectQuery.
SELECT
[tbl_OfferingInMovement].[ID] AS [ID],
[tbl_OfferingInMovement].[OfferingMovementID] AS [OfferingMovementID],
[tbl_OfferingInMovement].[OfferingID] AS [OfferingID],
[Offering].[Name] AS [OfferingName],
[Offering].[Code] AS [OfferingCode],
[Unit].[Name] AS [UnitName],
[OfferingMovement].[StoreID] AS [StoreID],
[tbl_Store].[Store] AS [Store],
[tbl_OfferingInMovement].[Quantity] AS [Quantity],
[tbl_OfferingInMovement].[BasicPrice] AS [BasicPrice],
[tbl_OfferingInMovement].[BasicAmount] AS [BasicAmount],
[tbl_OfferingInMovement].[SignedQuantity] AS [SignedQuantity],
[tbl_OfferingInMovement].[SignedAmount] AS [SignedAmount],
[tbl_OfferingInMovement].[Description] AS [Description],
[OfferingMovementType].[ID] AS [OfferingMovementTypeID],
[OfferingMovementType].[Name] AS [OfferingMovementType],
[tbl_OfferingInMovement].[PlanQuantity] AS [PlanQuantity],
[tbl_OfferingInMovement].[PlanAmount] AS [PlanAmount],
[OfferingMovement].[StatusID] AS [OfferingMovementStatusID],
[tbl_OfferingInMovement].[Price] AS [Price],
[tbl_OfferingInMovement].[Amount] AS [Amount],
[OfferingMovement].[CurrencyID] AS [CurrencyID],
[tbl_Currency].[Currency] AS [Currency],
[OfferingMovement].[CurrencyRate] AS [Rate],
[tbl_OfferingInMovement].[ConsignmentID] AS [ConsignmentID],
[Consignment].[Number] AS [ConsignmentNumber],
[Offering].[OfferingChargeMethodID] AS [OfferingChargeMethodID],
[OfferingMovement].[Number] AS [OfferingMovementNumber],
[tbl_OfferingInMovement].[TZR] AS [TZR],
[tbl_OfferingInMovement].[PrimeCost] AS [PrimeCost],
[tbl_OfferingInMovement].[PrimeAmount] AS [PrimeAmount],
[Offering].[Volume] AS [Volume],
[Offering].[Weight] AS [Weight],
[Offering].[BasicPrice] AS [OfferingBasicPrice],
[tbl_OfferingInMovement].[TZRAmount] AS [TZRAmount],
[tbl_OfferingInMovement].[DocumentID] AS [DocumentID],
[tbl_OfferingInMovement].[ContractID] AS [ContractID],
[tbl_OfferingInMovement].[InvoiceID] AS [InvoiceID],
[tbl_Document].[Title] AS [DocumentNumber],
[tbl_Contract].[Title] AS [ContractNumber],
[tbl_Invoice].[InvoiceNumber] AS [InvoiceNumber],
[tbl_OfferingInMovement].[PurchaseID] AS [PurchaseID],
[tbl_Purchases].[Number] AS [PurchaseNumber],
[OfferingMovement].[SupplierID] AS [SupplierID],
[tbl_OfferingInMovement].[Date] AS [Date],
[OfferingMovement].[ModifiedOn] AS [ModifiedOn],
[OfferingMovement].[DateOfShipment] AS [DateOfShipment],
[AccountBillingInfoItelon].[Name] AS [ItelonName],
[AccountBillingPayerInfo].[Name] AS [PayerName]
FROM
[dbo].[tbl_OfferingInMovement] AS [tbl_OfferingInMovement]
LEFT OUTER JOIN
[dbo].[vw_Offering] AS [Offering] ON [Offering].[ID] = [tbl_OfferingInMovement].[OfferingID]
LEFT OUTER JOIN
[dbo].[tbl_Unit] AS [Unit] ON [Unit].[ID] = [Offering].[DefaultUnitID]
LEFT OUTER JOIN
[dbo].[vw_OfferingMovement] AS [OfferingMovement] ON [OfferingMovement].[ID] = [tbl_OfferingInMovement].[OfferingMovementID]
LEFT OUTER JOIN
[dbo].[tbl_OfferingMovementType] AS [OfferingMovementType] ON [OfferingMovementType].[ID] = [OfferingMovement].[TypeID]
LEFT OUTER JOIN
[dbo].[tbl_Store] AS [tbl_Store] ON [tbl_Store].[ID] = [OfferingMovement].[StoreID]
LEFT OUTER JOIN
[dbo].[vw_OfferingMovement] AS [Consignment] ON [Consignment].[ID] = [tbl_OfferingInMovement].[ConsignmentID]
LEFT OUTER JOIN
[dbo].[tbl_Currency] AS [tbl_Currency] ON [tbl_Currency].[ID] = [OfferingMovement].[CurrencyID]
LEFT OUTER JOIN
[dbo].[vw_Document] AS [tbl_Document] ON [tbl_Document].[ID] = [tbl_OfferingInMovement].[DocumentID]
LEFT OUTER JOIN
[dbo].[vw_Contract] AS [tbl_Contract] ON [tbl_Contract].[ID] = [tbl_OfferingInMovement].[ContractID]
LEFT OUTER JOIN
[dbo].[vw_Invoice] AS [tbl_Invoice] ON [tbl_Invoice].[ID] = [tbl_OfferingInMovement].[InvoiceID]
LEFT OUTER JOIN
[dbo].[vw_Purchases] AS [tbl_Purchases] ON [tbl_Purchases].[ID] = [tbl_OfferingInMovement].[PurchaseID]
LEFT OUTER JOIN
[dbo].[tbl_AccountBillingInfo] AS [AccountBillingInfoItelon] ON [AccountBillingInfoItelon].[ID] = [OfferingMovement].[InfoItelonID]
LEFT OUTER JOIN
[dbo].[tbl_AccountBillingInfo] AS [tbl_AccountBillingInfoPayer] ON [tbl_AccountBillingInfoPayer].[ID] = [tbl_OfferingInMovement].[PayerID]
Предпоследний Join(AS [AccountBillingInfoItelon]) создан мною. Работает отлично.
Вот его код. Заранее извиняюсь за синтаксис VBA.
Set oTable = TSConnector.Services.GetNewItemByUSI(AccountBillingInfoTableName)
Set oJoin = oSelectQuery.Items(0).Joins.Items(4).CreateCopy
oJoin.UID = TSConnector.GenGUID
oJoin.RightField = oJoin.RightField.ParentFields.Items(40)
oJoin.LeftField = oTable.Fields.ItemsByName("ID")
oJoin.LeftTableAlias = "AccountBillingInfoItelon"
oJoin.JoinType = jtLeftOuter
oSelectQuery.Items(0).Joins.AddItem oJoin
oSelectQuery.Items(0).Joins.Items(oSelectQuery.Items(0).Joins.Count - 1).CanDisable = False
Последний создаётся не правильно.
Set oJoin = oInvoiceSelectQuery.Items(0).Joins.Items(0).CreateCopy
oJoin.UID = TSConnector.GenGUID
oJoin.LeftField = oTable.Fields.ItemsByName("ID")
oJoin.LeftTableAlias = "tbl_AccountBillingPayerInfo"
oJoin.RightField = TSConnector.Services.GetNewItemByUSI(InvoiceTableName).Fields.Items(31)
oJoin.JoinType = jtLeftOuter
oSelectQuery.Items(0).Joins.AddItem oJoin
oSelectQuery.Items(0).Joins.Items(oSelectQuery.Items(0).Joins.Count - 1).CanDisable = False
Проблема с RightField. Поле верное, то что нужно [PayerID]. Но мне надо чтобы оно доставала его именно из [tbl_Invoice]. А она же в SQL подставляет [tbl_OfferingInMoment]. Где можно изменить таблицу в Join'е или ещё где-то в SelectQuery. Целую неделю мучаюсь не могу понять.
Завтра с утра буду пробовать. Спасибо ещё раз. Отпишу что да как. 
