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

问题描述：

I have two tables (account and restaurant) How can I display all records from two tables but exclude some records?

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  |
+-------+----------+------------+--------------+

I want to display records from these two tables with the role admin and resto_owner. But also display the resto_name if the role is resto_owner, blank if admin and do not display if customer

I tried to use INNER JOIN but it only display: 2 Steve Resto_Owner Steve Resto and does NOT display the admin record:

Thank you in advance :)

我有两个表（帐户和餐馆）如何显示两个表中的所有记录但排除一些记录？

  table：account 
 + ------- + ---------- + ------------ +  
 |  uid | 名字| 角色| 
 + ------- + ---------- + ------------ + 
 |  1 | 约翰| 管理员| 
 |  2 | 史蒂夫|  Resto_Owner | 
 |  3 | 比尔| 客户| 
 + ------- + ---------- + ------------ + 
 
table：restaurant 
 + ------  -  + ---------- + ------------ + \ N | resto_id |  uid |  resto_name | 
 + -------- + ---------- + ------------ + 
 |  1 |  2 | Steve Resto | 
 + -------- + ---------- + ------------ + 
 
 
 **这是 我想要的输出：** 
 + ------- + ---------- + ------------ + ----------  ---- + \ N |  uid | 名字| 角色|  resto_name | 
 + ------- + ---------- + ------------ + --------------  + \ N |  1 | 约翰| 管理员|  | 
 |  2 | 史蒂夫|  Resto_Owner |  Steve Resto | 
 + ------- + ---------- + ------------ + -------------  -  + 
  code>  pre> 
 
  我想显示这两个表中带有admin和resto_owner角色的记录。 但是如果角色是resto_owner，也会显示resto_name，如果是admin，则显示为空白;如果客户 strong>  p>，则显示 p> 
 
 
我尝试使用INNER JOIN，但只显示： 2  Steve Resto_Owner Steve Resto  strong>并且不显示管理员记录： p> 
 
 
提前谢谢：） p> 
  div>

答

Use left join with conditions

 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

More in https://www.w3schools.com/sql/sql_join_left.asp

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

相关推荐