php - MySQL在一个查询中从表中获取相关和不相关的记录

Question

我有一个用户表和页表，这两个表与桥接表用户权限之间存在多对多关系。我想要获取所有页面名称，并且仅获取那些标记为分配给该用户的页面。

想要一些这样的想法

+---------+----------------------------------+
| user_id | page_name              | Assigned|
+---------+----------------------------------+
|       1 | Add Project            |   0     |
|       1 | Department             |   0     |
|       1 | Category               |   1     |
|       1 | Item                   |   0     |
|       1 | Units                  |   1     |
|       1 | Stock In               |   0     |
|       1 | Stock Card Report      |   1     |
+---------+------------------------+---------|

现在我的查询是这样的；

Select up.user_id, p.page_name FROM user_privileges up, pages p where p.page_id = up.page_id and up.user_id = 1;

它返回这个；

+---------+------------------------+
| user_id | page_name              |
+---------+------------------------+
|       1 | Add Project            |
|       1 | Department             |
|       1 | Category               |
|       1 | Item                   |
|       1 | Units                  |
|       1 | Stock In               |
|       1 | Stock Card Report      |
+---------+------------------------+

方案是这样的；表 - 用户

+---------+-----------+
| user_id | user_name |
+---------+-----------+
|       4 |  saif     |
|       1 | admin     |
|       5 | taqi      |
|       2 | rashid    |
+---------+-----------+

表格 - 页面

+---------+---------------+
| page_id | page_name     |
+---------+---------------+
|       2 | Page 1        |
|       3 | Page 2        |
|       5 | Page 3        |
|       6 | Page 4        |
|       7 | Page 5        |
|       8 | Page 6        |
|       9 | Page 7        |
|      10 | Page 8        |
|      11 | Page 9        |
|      13 | Page 10       |
|      14 | Page 11       |
|      15 | Page 12       |
|      16 | Page 13       |
|      18 | Page 14       |
|      19 | Page 15       |
|      20 | Page 16       | 
+---------+---------------+

和表 user_privalges 仅适用于 user_id = 1。

+--------------------+---------+---------+
| user_privileges_id | user_id | page_id |
+--------------------+---------+---------+
|                  1 |       1 |       2 |
|                  2 |       1 |       3 |
|                  3 |       1 |       5 |
|                  4 |       1 |       6 |
|                  5 |       1 |       7 |
|                  6 |       1 |       8 |
|                  7 |       1 |       9 |
|                  8 |       1 |      10 |
|                  9 |       1 |      11 |
|                 10 |       1 |      13 |
|                 11 |       1 |      14 |
|                 12 |       1 |      15 |
|                 13 |       1 |      16 |
|                 14 |       1 |      18 |
|                 15 |       1 |      19 |
|                 16 |       1 |      20 |
+--------------------+---------+---------+

Answer 1

Select up.user_id, p.page_name FROM user_privileges up, pages p where p.page_id = up.page_id and up.user_id = 1 AND up.Assigned=1;

“我想要获取所有页面名称，并且仅获取那些标记为分配给该用户的页面。”因此，在编辑时，您应该尝试使用 case 语句进行类似的操作

SELECT up.user_id, p.page_name,
 CASE 
  WHEN up.page_id=p.page_id THEN '1'   
  ELSE '0'
 END AS Assigned
FROM pages p left join user_privileges up 
ON p.page_id = up.page_id
WHERE up.user_id = 1;