I'll give u 3 hints but i can't go through ur requirements and solve it for you since it would take a few hours for me to do it.

1)draw ur erd and figure out the relationships between the tables, ie 1 to 1, 1 to many or many to many.

2)think subselect

3)an alternate to the join keyword is the where keyword.