Thanks for providing the table definitions. We'd be better off with correct SQL statement, but it's a start. Besides the DDL we also need the data itself (posted as INSERT statements). This data (multiple rows for each table) should cover all situations so it gives us insight in your problem.

Take a look at the first link in Lynn Pettis signature (see a few posts above). That's an article about how to post your questions and provide the required additional information to get the best answers.