Problems using INNER JOIN with using Composite Key

Question

I'm quite new to SQL and very new (learning today in fact!) how to use JOINS or in particular INNER JOIN. I have read some guides but haven't seen any helpful information for when one has a table with a composite key.

Tables:

Matches

+-----------+------------+
| (MatchID) | StartTime  |
+-----------+------------+
| 548576804 | 1393965361 |
| 548494906 | 1393123251 |
+-----------+------------+

And

+-----------------------------------+---------+
| (PlayerID)    -     (MatchID)    |   Result |
+-----------------------------------+---------+
| 38440257            548576804    |   Win    |
| 17164642            548494906    |   Loss   |
+-----------------------------------+---------+

Of the above tables, The MatchID in table Matches is a Foreign Key.

Problem

The columns in the parenthesis are Keys (so the composite key is in the MatchDetails table). I am trying to pull all of the Matches played by player 38440257, and the StartTime from the Matches table. The first Join I tried worked, however it pulled every game, regardless of player:

SELECT matchdetails.MatchID,
       matches.StartTime,
       matchdetails.Result
FROM matchdetails,
     matches
WHERE matchdetails.MatchID = matches.MatchID
ORDER BY matches.StartTime ASC

Now, I am not sure how to add in the point that I want ONLY matches from a particular playerID in the query. Because the following does not work:

SELECT matchdetails.MatchID,
       matches.StartTime,
       matchdetails.Result
FROM matchdetails,
     matches
WHERE matchdetails.MatchID = matches.MatchID,
      matchdetails.PlayerID=76561197998705985
ORDER BY matches.StartTime ASC

In addition, the JOIN I am using above, is there an easier way to write it that I am missing? Or am I not writing a Join at all? I followed one of the queries from here, which stated they were equivalent queries. However it feels rather cumbersome to write.

Please let me know if I have neglected any information.

Does the nonworking section return an error or incorrect data? — Eric Hauenstein, Mar 05 '14 at 14:47

score 4 · Accepted Answer · edited Jul 29 '20 at 18:25

You just need AND between your predicates:

SELECT matchdetails.MatchID,
       matches.StartTime,
       matchdetails.Result
FROM matchdetails,
     matches
WHERE matchdetails.MatchID = matches.MatchID
      AND matchdetails.PlayerID=76561197998705985
ORDER BY matches.StartTime ASC;

However, I would highly recommend you move away from the ANSI 89 JOIN syntax and adopt ANSI 92 instead. As the names suggest, the syntax you have used is over 20 years out of date.

SELECT matchdetails.MatchID,
       matches.StartTime,
       matchdetails.Result
FROM matchdetails
     INNER JOIN matches
        ON matchdetails.MatchID = matches.MatchID
WHERE matchdetails.PlayerID=76561197998705985
ORDER BY matches.StartTime ASC;

Thank you for the concise reply. I did think there was something not quite right about my JOIN query, but I wasn't sure what it was or how to find out what the problem was. I will change to the newer version and read up on it! — k4kuz0, Mar 05 '14 at 15:02

score 0 · Answer 2 · answered Mar 05 '14 at 14:48

0

SELECT matchdetails.MatchID,
       matches.StartTime,
       matchdetails.Result
FROM matchdetails 
     JOIN ON matchdetails.MatchID = matches.MatchID
WHERE
      matchdetails.PlayerID=76561197998705985
ORDER BY matches.StartTime ASC

answered Mar 05 '14 at 14:48

StanislavL

56,971
9
68
98

Problems using INNER JOIN with using Composite Key

Tables:

Problem

2 Answers2