我有3个表
roleuseruser_role
role包含role_id和role两行，
user包含user_id、fname、lname、mname、birthday。
user_role包含ur_id、role_id、user_id。
我想知道如何用teacher的role查询出所有的user？
类似这样的东西：
Fname | Lname | Role | birthdate
robert | foo | teacher | 10-12-15
您需要使用联接来获取输出。这可能有助于获得它。
Select u.fname as Fname, u.lname as Lname, r.role as Role, DATE_FORMAT(u.birtday,'%d-%m-%Y') as birthdate from user u
join user_role ur on u.user_id = ur.user_id
join role r on ur.role_id = r.role_id
where r.role = "teacher";
为了获得更好的输出，请用预期答案更新您的问题
内部加入教程请参阅下面的链接
http://www.mysqltutorial.org/mysql-inner-join.aspx
SELECT * FROM role
INNER JOIN user_role ON role.role_id = user_role.role_id
INNER JOIN user ON user_role.user_id= user.user_id
WHERE role.role="teacher";
SELECT u.user_id, u.fname, u.lname, u.mname, u.birth,r.role FROM user_role ur WHERE
ur.user_id=(SELECT u.user_id FROM user u) AND
ur.role_id=(SELECT r.role_id FROM role r)
尝试此查询。SELECT u.fname, u.lname, r.role, u.birthdate FROM user AS u, role AS r, user_role AS ur WHERE u.user_id = ur.user_id AND r.role_id = ur.role_id