How to implement a triple join on a single table
I am trying to figure out how to get a result that comes from a triple self join. I have a table that holds payments with some additional properties about the payment. In this case, I want to identify a payment record that has a matching subsequent payment with a variance in two properties and a third subsequent payment that matches but with a later date.
PaymentKey = 1, Amt = 10.00, Comm = 1.00 PaymentType = 1, PaymentDate = 2019-02-10, ReversingPaymentKey = 2
PaymentKey = 2, Amt = 10.00, Comm = 1.00 PaymentType = 2, PaymentDate = 2019-02-11, ReversingPaymentKey = 1
PaymentKey = 3, Amt = 10.00, Comm = 0.00 PaymentType = 1, PaymentDate = 2019-02-12, ReversingPaymentKey = null
In this case, Payment #1 satisfies the requirement and a flag should be put on the record indicating so.
I have tried to find documentation on the query() function, but have not been very successful. There is the one video on using it in an inQuery filter that seems to indicate a possible solution, but I cannot see it.
Is query() my answer? If so, how?
I have tried a number of attempts to see if I can get this to work. I have aliased the table twice and set joins for the appropriate keys. However, the joins don't work as expected. I have also played with the query() function to try to do dynamic joins, but I have not figured that out yet.
I had high hopes for the alias technique. Still looking for any help/guidance.
Update: I am very close now. I have an Incorta table that returns the exact same row count as the SQL query! The last half of this problem is getting the same count for the rows satisfying the final condition.
Inventory of artifacts
- 2 alias tables of the original payment table
- payment table joined to itself through the cross-referencing keys (not sure if this is needed)
- payment table joined to the 1st alias through the cross-referencing keys
- Incorta table providing rows of reversed payments forcing an inner join of artifact 3 (this rows set matches the SQL query count)
- Incorta table joined to the 2nd alias implicitly on payment type and an external foreign key
- Insight using artifact 4 and the 2nd alias with filters to match the amount and perform the date comparison (things get off in the count here)
Dan Brock - I still would like to review and get your insights.
Solved! With Dan 's help, I have correctly aligned the joins and have the triple join working as expected. The alias tables are needed to allow for the 2 joins needed to get the correct row sets. I did not need the self join as noted about (#2). I am keeping the Incorta table as it is a useful data set, but through experimentation, I achieved the same functionality with just the alias tables.