You can look up INNER JOIN and GROUP BY Google or Books Online
Using these two concepts, you can get the desired result
If you face any further issues get back to us with what you have tried,we would be happy to help you out..
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/