duandai0373
duandai0373
2018-06-10 16:26

Mysql php:如何连续多次加入同一个表?

已采纳
  • 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;
}
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

1条回答

  • duanbi1983 duanbi1983 3年前

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

    点赞 评论 复制链接分享

相关推荐