Thanks for posting ddl and sample data. There seems to be something a little wrong with your sample data. It will not run because it violates some of your foreign key constraints. If I recreate the tables and data without the constraints your query returns no rows.

In general it is easier if you don't have the database specified in the objects so it easier for use to work with. Also, you can get your code in a nice little scrollable window by using the IFCode shortcuts (over on the left when you are posting).

I think you are pretty close to having the information needed to figure this out.