I must be missing something obvious, but what is the correct way to join

transactionAccountingLine

to

transactionLine

? I thought this was correct

SELECT tl.transaction, tl.foreignamount, tal.credit, tal.debit
FROM transactionLine AS tl
JOIN transactionAccountingLine AS tal ON tl.transaction = tal.transaction AND tl.lineSequenceNumber = tal.transactionLine
WHERE tl.transaction = 21929383

But I have found a few cases where this gives incorrect joins. For a few transaction ids, the

transactionAccountingLine

has

transactionLine

values that don’t exist for that transaction id in

transactionLine.lineSequenceNumber

. For example, transactionLine has lineSequenceNumber values of 0, 1, 2, but in transactionAccountingLine, the only transactionLine values are 0, 2, 3. The 2 and 3 lines in

transactionAccountingLine

correspond to the 1 and 2 lines in

transactionLine

, respectively. I would expect the following query to return nothing, but it returns many results:

SELECT tal.transaction, tal.transactionLine
FROM transactionAccountingLine AS tal
WHERE tal.transaction > 21920000 -- limiting for performance
AND NOT EXISTS (SELECT tl.lineSequenceNumber FROM transactionLine AS tl WHERE tl.transaction = tal.transaction AND tl.lineSequenceNumber = tal.transactionLine)

Is there any documentation as to the proper way to join these tables? Thanks so much!

FROM Transaction INNER JOIN TransactionAccountingLine ON ( TransactionAccountingLine.Transaction = Transaction.ID ) LEFT OUTER JOIN TransactionLine ON ( TransactionLine.Transaction = TransactionAccountingLine.Transaction ) AND ( TransactionLine.LineSequenceNumber = TransactionAccountingLine.TransactionLine ) from @tdietrich Remote Library example

So, I think I had been mislead by that example, but I found out that while it seems to work most of the time, for 1 or 2 out of every 10,000 transaction lines, it’s incorrect. Joining TransactionAccountingLine.TransactionLine to TransactionLine.ID works correctly, though. @Clay Roper pointed me to the correct documentation from NetSuite in the Records Browser.