php - 如何在 PHP - MySQL 中连接两个表，它们与第二个表中的 ID 匹配，并且仍然在表 1 中显示特定记录？

Question

我有两个表(帐户和餐厅)，如何显示两个表中的所有记录但排除一些记录？

table: account
+-------+----------+------------+
| uid   | name     | role       |
+-------+----------+------------+
|  1    | John     | Admin      |
|  2    | Steve    | Resto_Owner|
|  3    | Bill     | Customer   |
+-------+----------+------------+

table: restaurant
+--------+----------+------------+
|resto_id|  uid     | resto_name |
+--------+----------+------------+
|  1     |   2      |Steve Resto |
+--------+----------+------------+


**This is my Desired Output:** 
+-------+----------+------------+--------------+
| uid   | name     | role       | resto_name   |
+-------+----------+------------+--------------+
|  1    | John     | Admin      |              |
|  2    | Steve    | Resto_Owner| Steve Resto  |
+-------+----------+------------+--------------+

我想显示这两个表中角色为 admin 和 resto_owner 的记录。但如果角色是 resto_owner，也会显示 resto_name，如果是 admin，则显示空白，如果是 customer，则不显示

我尝试使用 INNER JOIN 但它只显示:2 Steve Resto_Owner Steve Resto 并且不显示管理员记录:

提前谢谢你:)

Answer 1

使用带条件的左连接

 SELECT account_table.uid, account_table.name, account_table.role, restaurant_table.resto_name 
    FROM account account_table LEFT JOIN restaurant restaurant_table
    ON restaurant_table.uid = account_table.uid 
    WHERE account_table.role <> 'Customer' ORDER BY account_table.uid ASC

更多信息 https://www.w3schools.com/sql/sql_join_left.asp