Mysql php：如何连续多次加入同一个表？

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;
}

Answer 1

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).