如何在MySQL中將JOIN表增加三倍以使用OR運算元執行查詢[具體案例]

你好親愛的SO社區，

我已經為此苦苦掙扎了一段時間，由于時間不多了，我求助于你。

我有這些表：

USERS:
user_id (PK)
user_name

PROJECTS:
project_id (PK)
project_name
user_id (FK) - referring to the creator of the project

用戶和專案處于 M:N 關系，這在表 PERMISSIONS 中捕獲。

PERMISSIONS:
project_id (FK)
user_id (FK)

權限行包含單個用戶和單個專案的訪問規則。如果表中不存在給定用戶和專案的一對，則該用戶無法訪問該專案。

我正在嘗試查詢當前用戶應該有權訪問的所有專案，這意味著他創建的專案以及他在 PERMISSIONS 表中與之關聯的專案，但是對于每個專案，我都想要其創建者的用戶名。我將當前用戶 ID 提供給 PHP 中的準備好的陳述句，它作業正常，但我似乎無法正確查詢。我已經能夠

我能夠獲取某個用戶創建或訪問的專案，但我無法弄清楚如何加入 USERS，以獲取我查詢的專案中 user_id 的用戶名（專案創建者的用戶名）。

我正在查詢以下內容：

SELECT projects.name as name, projects.project_id as project_id, projects.user_id  as user_id, users.name as user_name
FROM projects JOIN permissions USING (project_id) JOIN users USING (user_id) WHERE permissions.user_id=:user_id OR 
projects.user_id=:user_id ORDER BY name

uj5u.com熱心網友回復：

有三種可能：

1. 有一個join帶有 , 的專案or來獲取創建者和有權限的人。通常不建議使用joinwith，因為它會導致表掃描。需要orDistinct

   select distinct u.user_name, p.project_name
   from users u
   left join permissions pm on pm.user_id = u.user_id
   left join projects p on p.user_id = u.user_id or p.project_id = pm.project_id
   where u.user_id = 2;
   

2. Cross join projects并在where子句中有'或'。需要distinct。

    select distinct u.user_name, p.project_name
    from users u
    cross join projects p
    left join permissions pm on pm.user_id = u.user_id
    where u.user_id = 2 and ( p.project_id = pm.project_id or  p.user_id = u.user_id );
   

3. 使用union，一種select用于創作者，一種用于獲得許可的人。Distinct不需要，因為union不需要distinct

      select u.user_name, p.project_name
      from users u
      inner join projects p on p.user_id = u.user_id
      where u.user_id = 2
    union 
      select u.user_name, p.project_name
      from users u
      inner join permissions pm on pm.user_id = u.user_id
      inner join projects p on p.project_id = pm.project_id
      where u.user_id = 2
   

在線編輯器：https ://onecompiler.com/mysql/3y64ukxtp

uj5u.com熱心網友回復：

您查詢中的問題是該user_id欄位應同時匹配：

• 在usersand之間permissions，捕捉允許用戶作業的專案
• 在usersand之間projects，捕捉用戶創建的專案

解決此問題的一種方法是UNION在這兩個結果集之間進行操作。為了讓join更高效，最好將過濾操作（查找特定用戶）移到join操作之前，這就是我將其移動到子查詢中的原因（在公共表運算式的形式下-cte） .

WITH user_details AS(
    SELECT user_id, 
           user_name 
    FROM users
    WHERE user_id = 501
)
SELECT user_details.*,
       projects.project_id,
       projects.project_name,
       projects.user_id         AS project_creator
FROM       user_details 
INNER JOIN projects     
        ON user_details.user_id = projects.user_id 

UNION 

SELECT user_details.*,
       projects.project_id,
       projects.project_name,
       projects.user_id         AS project_creator
FROM       user_details 
INNER JOIN permissions 
        ON user_details.user_id = permissions.user_id
INNER JOIN projects
        ON permissions.project_id = projects.project_id

您可以在此處的示例環境中試用它。

uj5u.com熱心網友回復：

使用此查詢：

SELECT project_id FROM projects WHERE user_id = :user_id
UNION ALL
SELECT project_id FROM permissions WHERE user_id = :user_id

:user_id您將獲得創建或有權訪問的所有專案的 ID 。

與運算子一起使用IN：

SELECT p.*, u.user_name
FROM projects p INNER JOIN users u
ON u.user_id = p.user_id
WHERE p.project_id IN (
  SELECT project_id FROM projects WHERE user_id = :user_id
  UNION ALL
  SELECT project_id FROM permissions WHERE user_id = :user_id
);

標籤：mysql sql 玛丽亚数据库

上一篇：將列值聚合到串列中會產生錯誤

下一篇：根據另一個表中的特定行選擇特定列