duandai0373 2018-06-10 16:26
浏览 105
已采纳

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 2018-06-10 16:43
    关注

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

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 有人能看一下我宿舍管理系统的报修功能该怎么改啊?链表那里总是越界
  • ¥15 cs loadimage运行不了,easyx也下了,没有用
  • ¥15 r包runway详细安装教程
  • ¥15 Html中读取Json文件中数据并制作表格
  • ¥15 谁有RH342练习环境
  • ¥15 STM32F407 DMA中断问题
  • ¥15 uniapp连接阿里云无法发布消息和订阅
  • ¥25 麦当劳点餐系统代码纠错
  • ¥15 轮班监督委员会问题。
  • ¥20 关于变压器的具体案例分析