SQL Query Join 2 tables

I'm new to SQL and was reading on joins but i'm a bit confused so wanted help....

I have a table called student_sport which stores StudentID and SportID

I have another table which stores details of matches... so basically sportsID,MatchId,....

What i wanna do is.... for a perticular student display the sports in which matches have been played. ie. if a sportID exists in the second table only then display that when i check which sports the student plays.

The resultSet should contain only those sports for a student in which matches have been played....

Thanks

Solution

Then you have two tables :

// One record per student / sport association
student_sport
    StudentID 
    SportID

// A match is only for one sport (warning to your plural) no?
matches
    SportID
    MacthID

You want: For one student all sport already played in a match

SELECT DISTINCT StudentID, student_sport.SportID
FROM student_sport, matches

WHERE 
      -- Select the appropriate player
      student_sport.StudentID = @StudentID    
      -- Search all sport played in a match and plays by the student 
      -- (common values sportid btw student_sport & matches)
  AND student_sport.SportID = matches.SportID

or use this other syntax (JOIN IN) (it makes complex queries easier to understand, so it's good to learn)

SELECT DISTINCT StudentID, student_sport.SportID
FROM student_sport
-- Search all sport played in a match and plays by the student 
-- (common values sportid btw student_sport & matches)
INNER JOIN matches on student_sport.SportID = matches.SportID
WHERE 
      -- Select the appropriate player
      student_sport.StudentID = @StudentID

ps: Includes Jan Hudec Coments, tx for it