mysql - 如何进行正确的加盟？

Question

您好，我有几个仪表板、用户、user_dahboard、user_organization 表。下面是表架构。

仪表板用户

id   user_id    dashboard_id
1       2         1
2       1         1
3       4         2
4       1         2

组织用户

id   user_id    org_id
1      1         1
2      2         1
3      3         2
4      4         2

仪表板

id  dashboard_title
1   World's Bank Data
2   Births
3   Unicode Test

现在我想从同一组织用户获取所有仪表板。如用户id 1可以看到同一组织用户开发的所有仪表板。我做了以下相同的查询

SELECT 
    dashboards.id AS dashboards_id 
FROM 
    dashboards 
    JOIN dashboard_user AS dashboard_user_1 ON dashboards.id = dashboard_user_1.dashboard_id 
        JOIN organization_user AS organization_user_1 ON organization.id = organization_user_1.org_id 
    JOIN ab_user ON ab_user.id = dashboard_user_1.user_id 
WHERE ab_user.id = 1

预期结果，如条件usr_id=1，它应该返回属于与user1具有相同组织的用户的所有dashboard_id。

结果

user    org      dashboard
1        1        1
2        1        2

Answer 1

我不确定任何 JOIN 是否必要。如果您只想要给定用户的仪表板:

SELECT du.dashboards_id 
FROM dashboard_user du
WHERE du.user_id = 1;

如果您希望所有仪表板都来自与 user_id = 1 相同的组织，则:

SELECT DISTINCT du.dashboards_id 
FROM dashboard_user du
WHERE du.user_id IN (SELECT ou.user_id
                     FROM org_user ou JOIN
                          org_user ou1
                          ON ou.org_id = ou1.org_id
                     WHERE ou1.user_id = 1
                    );

或者，作为JOIN:

SELECT DISTINCT du.dashboards_id 
FROM dashboard_user du JOIN
     org_user ou
     ON du.user_id = ou.user_id JOIN
     org_user ou1
     ON ou1.org_id = ou.ord_id AND
        ou1.user_id = 1