CODEWITHSUNDEEP
mysqljoin
Alan A
Alan A

Reputation: 2551

MySQL join strange results

Can anyone help, mysql is beyond me I think!

I have the following tables:

matches holds the details of a game between two teams and the corresponding competition.

I want to query the data base for all games/fixtures for a given competition, I want to return:

Here is my query:

SELECT
  matches.match_id,
  teamsh.team_name AS "homeTeam",
  teamsa.team_name AS "awayTeam",
  competition.competition_id,
  competition.name
FROM
  matches, teams teamsh, teams teamsa, competition
WHERE
  matches.home_team_id = teamsh.team_id
AND matches.away_team_id = teamsa.team_id
AND matches.competition_id=2

For some reason this query returns all fixtures correctly for competition 2, but then it also returns rows for the fixture but as competition 1 also. I can't understand why as I have the clause:

AND matches.competition_id=2

What am I doing wrong, I have checked the database and the matches are stored correctly for each fixture.

Thanks.

Upvotes: 1

Views: 73

Answers (2)

user359040
user359040

Reputation:

You haven't explicitly linked to the competition table (so it's performing a cartesian join) - try adding:

and matches.competition_id = competition.competition_id

- to the end of your query.

Although, I would suggest rewriting the query to use the explicit join syntax - like so:

SELECT m.match_id,
       h.team_name AS "homeTeam",
       a.team_name AS "awayTeam",
       c.competition_id,
       c.name
FROM matches m
JOIN teams h ON m.home_team_id = h.team_id
JOIN teams a ON m.away_team_id = a.team_id
JOIN competition c ON m.competition_id = c.competition_id
WHERE m.competition_id=2

Upvotes: 3

Loamhoof
Loamhoof

Reputation: 8293

You forgot the JOIN condition between the competitions and matches tables.

WHERE
  matches.home_team_id = teamsh.team_id
  AND matches.away_team_id = teamsa.team_id
  AND matches.competition_id = competition.competition_id
  AND matches.competition_id=2

Upvotes: 1

Related Questions