加入的表不返回任何结果

Question

我在代码的另一部分成功地加入了表格，所以我遵循了相同的模式，但这次它没有返回任何结果，我不明白为什么。以下是我要加入的表格：- 角色| id |  type  |+----+--------+|  1 | admin  ||  2 | author ||  3 | member |+----+--------+- 用户+----+----------+-------+----------+-------------------+--------+| id | username | email | password |      role_id      | status |+----+----------+-------+----------+-------------------+--------+|    |          |       |          | 3 (default value) |        |+----+----------+-------+----------+-------------------+--------+这是请求：public function get_users(){    $users_list = $this->dbh->query('SELECT users.id, roles.id AS roleid, type, role_id, id, username FROM users LEFT JOIN roles ON users.role_id = roles.id ORDER BY id ASC');    return $users_list;} 

Answer 1

SQL应该是

SELECT  

    u.id, 

    r.id AS roleid, 

    u.type,

    r.username

FROM users u

    LEFT JOIN roles r ON r.id = u.role_id

ORDER BY u.id ASC 

我省略了您在 sql 中定义的一些列，因为我根本看不到它们的意义。不仅如此，它们可能看起来模棱两可。

例如：

SELECT 

    users.id,             // User ID

    roles.id AS roleid,   // Role ID

    type,                 // User type

    role_id,              // Role ID (We already have it, no need for it again)

    id,                   // What ID? This is ambiguous and will fail 

    username              // User Username

Answer 2

像这样试试

public function get_users()

    {

        $users_list = $this->dbh->query('SELECT u.id AS userid, r.id AS roleid, r.type, u.role_id, u.username FROM users as u LEFT JOIN roles as r ON u.role_id = r.id ORDER BY u.id ASC');

        return $users_list;

    } 

Answer 3

由于两个表都有 id 列，因此需要指定哪个表引用<table_name>.id

public function get_users()

{

    $users_list = $this->dbh->query('SELECT users.id AS userid, roles.id AS roleid, type, role_id, username FROM users LEFT JOIN roles ON users.role_id = roles.id ORDER BY users.id ASC');

    return $users_list;

}