我的数据库由两个表组成
用户表

+---------+----------+---------------+-----------+
| user_id | username | password_hash | user_type |
+---------+----------+---------------+-----------+
| 1       | Admin    | hash          | 0         |
| 2       | Student  | hash          | 1         |
| 3       | Teacher  | hash          | 2         |
+---------+----------+---------------+-----------+

票务表

+-----------+---------+-----------------+----------------+--------------------+---------------+-------------+
| ticket_id | user_id | ticket_category | ticket_summary | ticket_description | ticket_status | assignee_id |
+-----------+---------+-----------------+----------------+--------------------+---------------+-------------+
| 1         | 2       | Network         | Test Network   | Lorem Ipsum        | 1             | 1           |
| 2         | 3       | Printer         | Test Printer   | Lorem Ipsum        | 1             |             |
+-----------+---------+-----------------+----------------+--------------------+---------------+-------------+

tickets.user\u id和tickets.assignee\u id都是users.user\u id的外键
我使用下面的select语句查询这些表

SELECT username AS `Opened By`, assignee_id AS `Assigned To`, ticket_category AS `Category`, ticket_summary AS `Summary`, ticket_description AS `Description`, IF(ticket_status = 1,'Open','Closed') AS `Status` FROM tickets INNER JOIN users ON tickets.user_id = users.user_id;

这就建立了这样一个数据集

+-----------+-------------+----------+--------------+-------------+--------+
| Opened By | Assigned To | Category | Summary      | Description | Status |
+-----------+-------------+----------+--------------+-------------+--------+
| Student   | 1           | Network  | Test Network | Lorem Ipsum | Open   |
| Teacher   |             | Printer  | Test Printer | Lorem Ipsum | Open   |
+-----------+-------------+----------+--------------+-------------+--------+

在我的select语句中，我想用users表中相应的用户名替换受让人的id号。
例如，第一张票证（票证id=1）的受让人id为1，我希望assigned to列显示用户名“admin”而不是1
如何修改select查询来实现这一点？