I think it would help to know more about the the table structure (at least the fields involved in the join/where clauses), some sample data to test against, and an expected results based on the sample data (to check tests against).

it looks like a self join using a correlated subquery. Knowing more about the data will help in figuring out how to join things together.

😎