0

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.

For example:

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?

6replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • 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.

    Reply Like
    •  The joins were not set up correctly. That has been fixed and now the first part of the problem is working.  That is Payment 1 and payment 2 show correctly in a table insight. I am using an inQuery filter to try to get the Payment 3 portion, but it is taking a very long time to load. The inQuery filter uses the query() function with the filters set to match the amount, type, commission, and date comparison. Ideally, the additional join needs four conditions. I will try to create that join, but I have not been successful in multiple condition joins in the past.

      Reply Like
    • Marc Paige Marc, please reach out to me directly and we will setup sometime to look at your issue directly.

      Reply Like
    •  Dan, ok. I am experimenting with an Incorta table now. It occurred to me last night that I should be able to get an initial row set from the new join and then use that as an Incorta table to join back to the table to get the desired results. I will reach out regardless.

      Reply Like
  • 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

    1. 2 alias tables of the original payment table
    2.  payment table joined to itself through the cross-referencing keys (not sure if this is needed)
    3. payment table joined to the 1st alias through the cross-referencing keys
    4. Incorta table providing rows of reversed payments forcing an inner join of artifact 3 (this rows set matches the SQL query count)
    5. Incorta table joined to the 2nd alias implicitly on payment type and an external foreign key
    6. 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.

    Reply Like
  • 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.

    Reply Like
Like Follow
  • Status Answered
  • 2 mths agoLast active
  • 6Replies
  • 75Views
  • 3 Following