Viewing data from one column twice in SQL

Hi guys,

SOrry if the title is a bit misleading. I am a novice just getting into SQL.

Does anyone know how i would go about trying to gain information from one coloumn to output different values.

For example lets say i have a football team. I would like to know the names of all the people that have scored a goal from a penalty, but I would also like to know the name of the person who gave away the penalty. Does anyone know how i would start to go about doing this. Would i need a view, join (inner, outer, left, right) etc ?