mysql - 如果 ID 匹配则内连接追加

Question

餐 table 人员:

ID, NAME, AGE, DESCRIPTION, STATUS

值(value)观:

1, Casey, 18, A fresh grad, Probation
2, Wedy, 25, Middle career, Approve
3, Stacy, 33, A working professional, Confirmed
4, Steve, 45, Senior Management, Confirmed
5, Goat, 55, Retire, Rejected

<小时/>

表权限:

PermID, TYPE, VALUE

值(value)观:

1, normaluser, can view training 1 and 2
1, probationuser, can view training 3 part 1
2, normaluser, can view training 1 and 2
2, traininguser, can view training 3
2, extradinoaryuser, Wendy can view full training
3, extradinoaryuser, Stacy can view full training
3, superuser, Stacy also can edit full training
3, supertrainer, can create full training

<小时/>

努力实现:

1. 显示完整的人员表数据，其中“status”= 已批准或“status”= 已确认

输出 - 显示人员行 ID 2,3,4

如果将 Person ID 与 Permission PermID 匹配，则获取 'type' = extradinoaryuser 或 'type' = superuser，忽略其余类型，与 Person 表数据合并。

输出 - 附加权限“类型”和“值”人员行 ID 2,3

如果条目同时具有 super 用户和额外用户类型，请复制该条目

输出 - 人员 ID 第 3 行被复制为 super 用户和 extradinoaryuser

期望的结果:

2, Wedy, 25, Middle career, Approve, extradinoaryuser, Wendy can view full training<br/>
3, Stacy, 33, A working professional, Confirmed, extradinoaryuser, Stacy can view full training<br/>
3, Stacy, 33, A working professional, Confirmed, superuser, Stacy also can edit full training<br/>
4, Steve, 45, Senior Management, Confirmed

<小时/>

Sql 命令(不起作用):

select Person.id, Person.name, Person.age, Person.description, Person.status 
from Person  
inner join Permission ON Person.id = Permission.PermID 
where Person.status like “Approve”  
   or Person.status like “Confirmed” 
  AND (permission.name = 'superuser' or permission.name = ‘extradinoaryuser’); 

Answer 1

对字符串变量使用正确的引号( '' ):

select p.id, p.name, p.age, p.description, p.status 
  from Person p inner join Permission m ON ( p.id = m.PermID )
 where p.status like 'Approve' or p.status like 'Confirmed'
   and (m.name = 'superuser' or m.name = 'extradinoaryuser');

附注确保表中字符串值的(小写/大写)大小写。