CODEWITHSUNDEEP
mysqldatabasejoinsubqueryleft-join
Mitya
Mitya

Reputation: 34566

MySQL - join on same table?

I'm trying to implement this answer, which shows how, using table aliases, you can effectively join to the same table you're running the query on.

Consider the following (simplified) table of football teams:

-----------------------------------------------------
| id | name      | next_fixture_against | url_token |
=====================================================
| 1  | Hull City | 2                    | hull      |
-----------------------------------------------------
| 2  | Everton   | 1                    | everton   |
-----------------------------------------------------

I'm trying to write a query that fetches the names of a given team and its next opponent.

Approach 1: left join

Here's what I came up with by adapting the above answer:

SELECT main_team.name, opposition_team.name as against
FROM teams AS main_team
LEFT JOIN teams AS opposition_team
ON main_team.name = opposition_team.next_fixture_against
WHERE main_team.url_token = 'hull'

That produces:

-----------------------
| name      | against |
=======================
| Hull City | NULL    |
-----------------------

If I remove the WHERE clause, it does work, but, strangely, it comes out in the reverse order from that which I'd expect, i.e.

-----------------------
| name    | against   |
=======================
| Everton | Hull City |
-----------------------

rather than name = Hull City, against = Everton.

(This is purely a point of interest - clearly I need the where clause as I'm targeting a particular team, not all teams.)

Approach 2: sub-query

I came up with the following, alternate approach:

SELECT
    name,
    (SELECT name FROM teams WHERE id = ???) AS against
FROM
    teams
WHERE
    url_token = 'hull'

...but I'm not sure what to replace ??? with to get the inner sub-query to reference the outer one.

Can anyone point me in the right direction? Thanks in advance.

Upvotes: 0

Views: 50

Answers (1)

O. Jones
O. Jones

Reputation: 108706

You need ON main_team.id = opposition_team.next_fixture_against. You tried to use the team name rather than id in the join.

Don't worry, we've all done this.

Upvotes: 1

Related Questions