Hi,

I hope someone can give me some help here.

I've inherited a database full of sp's written in a similar fashion to the
first statement below (SQL-92 standard is it?). I'm having difficulty following
the syntax in the FROM clause for the INNER JOIN's and am looking for pointers
in how to follow the syntax. For example if I wanted to debug the sql and
see if a table had missing data I need to split the FROM clause up into smaller
SELECT statements to see where the missing data is.

The second statement is more readable (to me anyway), and I've checked that
both produce the same results - I take it that this stmt is pre the SQL-92
standard?

Any help/pointers are greatly appreciated.

TIA, Neil.

SELECT dbo.tblTrades.TradeID, dbo.tblTradeLines.PartID, dbo.tblSubProducts400.DealType,
GETDATE() AS InputDate, GETDATE() AS InputTime,
dbo.tblTrades.TradeDate AS BusinessDayDate, BCP400.Code
AS BorrowerCode, BCP400.Location AS BorrowerLoc, BCP400.Agent AS BorrowerAgent,

OCP400.Code AS LenderCode, OCP400.Location AS LenderLoc,
OCP400.Agent AS LenderAgent, BD400.DealerNo AS BorrowerDealerNo,
OD400.DealerNo AS LenderDealerNo, dbo.tblTradeLines.LineMillions,

dbo.tblTradeLines.LineMillions * 1000000 - dbo.tblTradeLines.LineAmount
AS LineAmount, dbo.tblTradeLines.LineRate, dbo.tblTrades.SettlementDate,

dbo.tblTradeLines.MaturityDate, dbo.tblTradeLines.LenderBroCode,
dbo.tblTradeLines.LenderBroAmount, dbo.tblTradeLines.BorrowerBroCode,
dbo.tblTradeLines.BorrowerBroAmount, dbo.tblTradeLines.ReposSpecialInstFlag,
dbo.tblTradeLines.ReposSpecialInst AS SpecialInstruction,
dbo.tblTradeLines.BondsYield, dbo.tblTradeLines.BondsIssuerCode,
dbo.tblTradeLines.BondsIssuerLocID, dbo.tblTradeLines.BillsAcceptorNarr,

dbo.tblTradeLines.BillsDrawerNarr, dbo.tblTradeLines.RepoYield,
dbo.tblTrades.StartDate, tblCurrencies.CurrencyCode400 AS Currency,
dbo.tblTradesAS400ActionMap.ActionCode AS [Action],
dbo.tblTrades.ProcessID, dbo.tblTradeLines.AS400ActionID, dbo.tblTradeLines.AS400StatusID,

dbo.tblSubProducts400.SubProductID
FROM dbo.tblSubProducts400 INNER JOIN
dbo.tblSubProducts ON dbo.tblSubProducts400.SubProductID
= dbo.tblSubProducts.SubProductID INNER JOIN
dbo.tblTrades INNER JOIN
dbo.tblTradeLines ON dbo.tblTrades.TradeID = dbo.tblTradeLines.TradeID
ON
dbo.tblSubProducts.SubProductID = dbo.tblTradeLines.SubProductID
INNER JOIN
TTLAUsers.dbo.tblClients BCP ON dbo.tblTrades.BorrowerID
= BCP.ClientID INNER JOIN
TTLAUsers.dbo.tblClients400 BCP400 ON BCP.ClientID
= BCP400.ClientID AND dbo.tblTradeLines.SubProductID = BCP400.SubProductID
INNER JOIN
TTLAUsers.dbo.tblClients OCP ON dbo.tblTrades.LenderID
= OCP.ClientID INNER JOIN
TTLAUsers.dbo.tblClients400 OCP400 ON OCP.ClientID
= OCP400.ClientID AND dbo.tblTradeLines.SubProductID = OCP400.SubProductID
INNER JOIN
TTLAUsers.dbo.tblUsers BD ON dbo.tblTrades.BorrowerDealerNo
= BD.UserID INNER JOIN
TTLAUsers.dbo.tblUsers400 BD400 ON BD.UserID = BD400.UserID
AND dbo.tblSubProducts.ProductID = BD400.ProductID INNER JOIN
TTLAUsers.dbo.tblUsers OD ON dbo.tblTrades.LenderDealerNo
= OD.UserID INNER JOIN
TTLAUsers.dbo.tblUsers400 OD400 ON OD.UserID = OD400.UserID
AND dbo.tblSubProducts.ProductID = OD400.ProductID INNER JOIN
TTLAUsers.dbo.tblCurrencies tblCurrencies ON tblCurrencies.CurrencyID
= dbo.tblTrades.CurrencyID INNER JOIN
dbo.tblTradesAS400ActionMap ON dbo.tblTradeLines.AS400ActionID
= dbo.tblTradesAS400ActionMap.AS400ActionID

where dbo.tblTrades.TradeID = 1524

SELECT dbo.tblTrades.TradeID, dbo.tblTradeLines.PartID, dbo.tblSubProducts400.DealType,
GETDATE() AS InputDate, GETDATE() AS InputTime,
dbo.tblTrades.TradeDate AS BusinessDayDate, BCP400.Code AS BorrowerCode,
BCP400.Location AS BorrowerLoc, BCP400.Agent AS BorrowerAgent,
OCP400.Code AS LenderCode, OCP400.Location AS LenderLoc, OCP400.Agent
AS LenderAgent, BD400.DealerNo AS BorrowerDealerNo,
OD400.DealerNo AS LenderDealerNo, dbo.tblTradeLines.LineMillions,

dbo.tblTradeLines.LineMillions * 1000000 - dbo.tblTradeLines.LineAmount
AS LineAmount, dbo.tblTradeLines.LineRate, dbo.tblTrades.SettlementDate,

dbo.tblTradeLines.MaturityDate, dbo.tblTradeLines.LenderBroCode,
dbo.tblTradeLines.LenderBroAmount, dbo.tblTradeLines.BorrowerBroCode,
dbo.tblTradeLines.BorrowerBroAmount, dbo.tblTradeLines.ReposSpecialInstFlag,
dbo.tblTradeLines.ReposSpecialInst AS SpecialInstruction,
dbo.tblTradeLines.BondsYield, dbo.tblTradeLines.BondsIssuerCode,
dbo.tblTradeLines.BondsIssuerLocID, dbo.tblTradeLines.BillsAcceptorNarr,

dbo.tblTradeLines.BillsDrawerNarr, dbo.tblTradeLines.RepoYield,
dbo.tblTrades.StartDate, tblCurrencies.CurrencyCode400 AS Currency,
dbo.tblTradesAS400ActionMap.ActionCode AS [Action], dbo.tblTrades.ProcessID,
dbo.tblTradeLines.AS400ActionID, dbo.tblTradeLines.AS400StatusID,
dbo.tblSubProducts400.SubProductID
FROM dbo.tblSubProducts400,
dbo.tblSubProducts,
dbo.tblTrades,
dbo.tblTradeLines,
TTLAUsers.dbo.tblClients BCP,
TTLAUsers.dbo.tblClients400 BCP400,
TTLAUsers.dbo.tblClients OCP,
TTLAUsers.dbo.tblClients400 OCP400,
TTLAUsers.dbo.tblUsers BD,
TTLAUsers.dbo.tblUsers400 BD400,
TTLAUsers.dbo.tblUsers OD,
TTLAUsers.dbo.tblUsers400 OD400,
TTLAUsers.dbo.tblCurrencies tblCurrencies,
dbo.tblTradesAS400ActionMap
WHERE dbo.tblTrades.TradeID = 1524 AND
dbo.tblSubProducts400.SubProductID = dbo.tblSubProducts.SubProductID AND

dbo.tblTrades.TradeID = dbo.tblTradeLines.TradeID AND
dbo.tblSubProducts.SubProductID = dbo.tblTradeLines.SubProductID AND
dbo.tblTrades.BorrowerID = BCP.ClientID AND
BCP.ClientID = BCP400.ClientID AND
dbo.tblTradeLines.SubProductID = BCP400.SubProductID AND
dbo.tblTrades.LenderID = OCP.ClientID AND
OCP.ClientID = OCP400.ClientID AND
dbo.tblTradeLines.SubProductID = OCP400.SubProductID AND
dbo.tblTrades.BorrowerDealerNo = BD.UserID AND
BD.UserID = BD400.UserID AND
dbo.tblSubProducts.ProductID = BD400.ProductID AND
dbo.tblTrades.LenderDealerNo = OD.UserID AND
OD.UserID = OD400.UserID AND dbo.tblSubProducts.ProductID = OD400.ProductID
AND
tblCurrencies.CurrencyID = dbo.tblTrades.CurrencyID AND
dbo.tblTradeLines.AS400ActionID = dbo.tblTradesAS400ActionMap.AS400ActionID