CODEWITHSUNDEEP
mysqlsqljoin
k4kuz0
k4kuz0

Reputation: 1045

Problems using INNER JOIN with using Composite Key

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.

Upvotes: 0

Views: 407

Answers (2)

StanislavL
StanislavL

Reputation: 57421

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

Upvotes: 0

GarethD
GarethD

Reputation: 69789

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;

Upvotes: 4

Related Questions