When you are building your sub-SELECT statement, a tip would be to first get the subquery running by working with it against your table. When you have it returning the results you expect and need, then you can build the outer SELECT statement around it.
This will make it easier to troubleshoot your query, and will make sure you know exactly what the results are that you are expecting.
Subselects allow you to

1. query within a result set,
2. creatively limit your result set,
3. or correlate your results with an otherwise unrelated query in a single call to the database.