CODEWITHSUNDEEP
mysqlsqljoin
Alan A
Alan A

Reputation: 2551

mysql simple join

I am trying to perform a simple mysql join:

I have a tables of matches that features two fields which I want to join on: team1 and team2.

I want to find the corresponding name of the team which is stored in the teams table:

SELECT teams.team_name AS "name1", teams.team_name AS "name2", matches.id
FROM teams, matches 
WHERE matches.id=1
AND matches.team1_id=teams.team_id
AND matches.team2_id=teams.team_id

If I remove either of the last and conditions I get a result, but if I include both I get an empty set?

What am I doing wrong?

Upvotes: 0

Views: 1867

Answers (4)

John Woo
John Woo

Reputation: 263693

You need to join teams twice on table matches since two of the columns are dependent on it,

SELECT  a.*,                -- <<== select column that you want to project
        b.team_name AS Team1Name,
        c.Team_name AS Team2Name
FROM    matches a
        INNER JOIN teams b
            ON a.team1_ID = b.team_ID
        INNER JOIN teams c
            ON a.team2_ID = c.team_ID
-- WHERE a.id = 1

To further gain more knowledge about joins, kindly visit the link below:

Upvotes: 2

Matteo Tassinari
Matteo Tassinari

Reputation: 18584

Try this:

SELECT team1.team_name AS "name1", team2.team_name AS "name2", matches.id
FROM teams as team1, teams as team2, matches 
WHERE matches.id=1
AND matches.team1_id=team1.team_id
AND matches.team2_id=team2.team_id

Upvotes: 0

Joe Enos
Joe Enos

Reputation: 40393

Your query as it is will only return records where the team plays against itself (which of course never happens). You want two different joins against team, so two instances of it:

select
    teams1.team_name as "name1"
    ,teams2.team_name as "name2"
from
    matches
join
    teams teams1 on matches.team1_id = teams1.team_id
join
    teams teams2 on matches.team2_id = teams2.team_id
where
    matches.id = 1

Upvotes: 0

Phill Sparks
Phill Sparks

Reputation: 20879

For each team you want to reference uniquely in your query you need a separate alias, otherwise SQL will compare the same row.

SELECT team1.team_name AS "name1", team2.team_name AS "name2", matches.id
FROM teams team1, teams team2, matches 
WHERE matches.id=1
AND matches.team1_id=team1.team_id
AND matches.team2_id=team2.team_id

Here we have now 2 aliases of your teams table for team1 and team2, so they can each refer to different rows.

Upvotes: 3

Related Questions