SQL Query Join 2 tables

Question

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

This is not so difficult. Since this is a homework question, it is better you look for it. Here is a hint : google for "inner join" — ali_bahoo, Oct 03 '11 at 13:34
i was thinking something like SELECT * FROM TABLE1 WHERE EXISTS(SELECT * FROM TABLE2) but not sure how to do it for a perticulat student — John, Oct 03 '11 at 13:35
@John: Subquery is overcomplicating things here with subqueries. The subquery will give you just which rows have corresponding row in the other table while join gives you all combinations of corresponding rows. And you do want the bits from the other table, right? — Jan Hudec, Oct 03 '11 at 13:43

score 3 · Answer 1 · answered Oct 03 '11 at 13:35

Okay, as this is homework (thanks for being honest about that) I won't provide a solution.

Generic way to design a query is:

Think of how to write the FROM block (what is your data source)
Think of how to write the WHERE block (what filters apply)
Write the SELECT block (what you want from the filtered data).

You obviously need to join your two tables. The syntax for joins is:

FROM table1
JOIN table2 ON boolean_condition

Here your boolean_condition is equality between the columns sportid in your two tables.

You also need to filter the records that your join will produce, in order to only retain those that match your particular student, with the WHERE clause.

After that, select what you need (you want all sports ids).

Does that help enough?

+1 for giving an explanation instead of just the exact answer — ali_bahoo, Oct 03 '11 at 13:42

Guilhem Hoffmann · Accepted Answer · 2011-10-04T08:25:54.897

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

It's right, but two nits: 1. I'd add (add, not use only; it's important to know they mean the same) the equivalent join/on syntax; it makes complex queries easier to understand, so it's good to learn and 2. please use standard SQL comments using the `--` leader, since all databases accept those, more accept C-style comments and few accept C++-style ones you used. — Jan Hudec, Oct 03 '11 at 13:48

score 0 · Answer 3 · answered Oct 03 '11 at 13:49

Because you only need to return results from the student_sport table, the join type is a semijoin. Standard SQL's operator for semi join is funnily enough MATCH e.g.

SELECT * 
  FROM student_sport 
 WHERE SportID MATCH (
                      SELECT SportID   
                        FROM matches
                       WHERE student_sport.SportID = matches.SportID
                     );

There are a number of other ways of writing a semi join in SQL e.g. here's three more:

SELECT * 
  FROM student_sport 
 WHERE SportID IN (
                   SELECT SportID  
                     FROM matches
                    WHERE student_sport.SportID = matches.SportID
                  );

SELECT * 
  FROM student_sport 
 WHERE EXISTS (
               SELECT * 
                 FROM matches
                WHERE student_sport.SportID = matches.SportID
              );

SELECT student_sport.* 
  FROM student_sport 
       INNER JOIN matches
          ON student_sport.SportID = matches.SportID;

score -1 · Answer 4 · answered Oct 03 '11 at 13:33

-1

Well the query would be something like that:

select sm.* 
from student_sport ss join student_matches sm on ss.sportid = sm.sportId
where ss.StudentId = @studendId

This and this should give you some insight of sql joins

answered Oct 03 '11 at 13:33

Iridio

9,213
4
49
71

SQL Query Join 2 tables

4 Answers4