Hiya. I'm joining on the previoustransactionlinelink table with previousdoc and previousline. However, it seems that on some transactions the previousline is failing. When I run just on previousdocwith

where previousdoc = 1931587 and nexttype = 'ItemShip'

I get the following:

{

"records": [

{

"foreignamount": null,

"previoustype": "SalesOrd",

"lastmodifieddate": "3/15/2023",

"nextline": 0,

"previousline": 1,

"nexttype": "ItemShip",

"previousdoc": 1931587,

"nextdoc": 1959659,

"linktype": "ShipRcpt"

}

]

}

However, when I add in

where previousdoc = 1931587 and nexttype = 'ItemShip' and previousline = 1

it gives an empty result set. Thanks for any assistance.

The conditions should be part of the ON clause of the join, not the WHERE clause.

Thank you for the suggestion. This also is still returning blank record set.

SELECT     transaction.id AS transaction_id,
           transactionline.id AS transactionline_id,
           previoustransactionline.transaction AS previoustransaction_id,
           previoustransactionline.id AS previoustransactionline_id
FROM       transaction
INNER JOIN transactionline ON (transactionline.transaction = transaction.id)
LEFT JOIN  nexttransactionlinelink ON (nexttransactionlinelink.nextdoc = transaction.id AND
                                       nexttransactionlinelink.nextline = transactionline.id)
LEFT JOIN  transaction previoustransaction ON (previoustransaction.id = nexttransactionlinelink.previousdoc)
LEFT JOIN  transactionline previoustransactionline ON (
             previoustransactionline.transaction = nexttransactionlinelink.previousdoc AND
             previoustransactionline.id = nexttransactionlinelink.previousline)
WHERE transaction.id = 123

Example finding the parent transactionline of transaction #123

I'll review, my query works in 85% of cases, so I'm not sure what is different about these missing transactions. Thanks for the suggestions, I'll see if they can help me figure it out.