Can This Be Made Into A Query

I am trying to make a query that would list all the rows in a table called tickets. It should order the results based on the amount of rows another table has, called rating, but only the rows where the ticketid (primary key) matches and where the date is a required date or higher. It should also order them based on the osid which is located in a systems table (there is a relationship between tickets and systems).
 
So:
 Order by rows in ratings table relating to ticketid that happened in last week (or another min date)Only display it where osid in systems matchs (easy enough)