跨多个表的MySQL查询问题

Sorry for what is perhaps a trivial question to those MySQL or SQL gurus in general. I have 3 tables:

很抱歉,对于那些MySQL或SQL大师而言,这可能是一个微不足道的问题。我有3张桌子:

• users
• pages
• security

security has several levels like: guest, standard user, admin, etc. This is an integer value. So basically a guest is 0, standard is 1, admin is 10.

安全性有几个级别,如:来宾,标准用户,管理员等。这是一个整数值。所以基本上客人是0,标准是1,管理员是10。

each user has a security id for lookup in the security table.

每个用户都有一个安全ID,可以在安全表中查找。

each page has a security id for lookup as to which page can be seen for a given user.

每个页面都有一个安全ID,用于查找给定用户可以看到哪个页面。

I would like to create a query that using the current username, return the pages this user can see. So basically return all the pages with a security level less than or equal to the user's security level. But I am not storing the level in each table just the ID to the security table unique entry.

我想创建一个使用当前用户名的查询,返回该用户可以看到的页面。所以基本上返回安全级别小于或等于用户安全级别的所有页面。但是我没有将每个表中的级别只存储到安全表唯一条目的ID。

I have attached a screenshot of my DB schema as it stands:

我附上了我的数据库架构的屏幕截图:

Sample Data:

users table:

pages table:

security table:

2 个解决方案

#1

SELECT p.name FROM users u JOIN security s ON u.security_id=s.id 
      JOIN pages p ON s.id=p.security_id where u.username='currentUser' 
AND    (SELECT level from security s JOIN users u on  s.id=u.security_id where u.username='currentUser' GROUP BY level ) >= (SELECT level from security s JOIN pages p on  s.id=p.security_id JOIN users u on  s.id=u.security_id where u.username='currentUser' GROUP BY level)

#2

Try this

select
   p.name
from
   pages p,
   security s,
   users u
where u.security_id = s.id
  and p.security_id = s.id
  and u.username = 'someuser'