Mysql php: How to join same table multiple times?

Question

Team table has:

   ID   |   TEAM  
--------+----------
   1    |     A
   2    |     B

Result table has:

  fk_ID1  |   fk_ID2  |  RESULT
----------+-----------+-----------
    1     |     2     |   5:0
    2     |     1     |   2:3

How to Inner JOIN table, to get: (A 5:0 B) & (A 2:3 B)?

My code example:

public function getResultList($limit, $offset) {
    $query = "  SELECT result_id,
                       t1.name name1,
                       t2.name name2,
                       team1_goals,
                       team2_goals,
                       date
                FROM results
                    INNER JOIN team t1 ON fk_tm1_id=tm_id
                    INNER JOIN team t2 ON fk_tm2_id=tm_id";
            $data = mysql::select($query);
    return $data;
}

Put and format your code in the post, don't attach it as a image. — Devon Bessemer
– Devon Bessemer, Commented Jun 10, 2018 at 16:28

Devon Bessemer · Accepted Answer · 2018-06-10 16:43:54Z

2

It's best to answer this as purely an SQL question, which it is. You need to assign a table alias when joining the same table two or more times.

You seem to only be assigning aliases to the column. To assign an alias to a column or table, you can add the alias directly after the column or table name (AS can also be used but isn't necessary for MySQL)

A common thing is to number the tables as t1, t2, t3, etc.

SELECT t1.name name1, t2.name name2 FROM ...
INNER JOIN team_table t1 ON ...
INNER JOIN team_table t2 ON ...

This aliases the first join as t1 and the second join as t2, which you would use when accessing data from that specific join (SELECT t1.name).

answered Jun 10, 2018 at 16:43

Devon Bessemer

35.6k10 gold badges77 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Artem Ilchenko Over a year ago

and add WHERE clause to exclude results for same ids

Luke Over a year ago

@Devon, updated, tried, not working... Maybe something I've missed?

Eight Lives Over a year ago

I agree, this answer is pretty good but needs further explanation where the ellipsis exist, (...). I realize this is where you would insert this information in your own situation but the main challenge is the syntax of the solution which is partially left out.

Collectives™ on Stack Overflow

Mysql php: How to join same table multiple times?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related