Write a SQL query to retrieve data from a given database table based on specific join and filtering criteria.
