MySQL-两次连接同一张表，但结果不同 [英] MySQL - Joining the same table twice but grabbing different results

问题描述

我之前曾问过关于同一情况的问题

I previously asked a question in regards to the same situation here but since I was so vague about my query, the provided solution didn't work for my specific case. Here's my second attempt at resolving this:

我有一系列以奇怪​​的方式连接的表...这是我试图通过SELECT查询获得的最终结果:

I have a series of tables that are connected in strange ways... Here's the end result I'm trying to achieve with my SELECT query:

|----|---------|----------|----------|---------------|---------------|------------|---------------|
| id | company | city     | province | manager_name  | manager_email | staff_name | staff_email   |
|----|---------|----------|----------|---------------|---------------|------------|---------------|
| 1  | aaa     | toronto  | ON       | John Smith    | [e-mail]  | Steve Chan | [e-mail] |
| 2  | bbb     | sudbury  | ON       | Tom Bell      | [e-mail]   |            |               |
| 3  | ccc     | hamilton | ON       | Bill Miller   | [e-mail]  | Jill Smith | [e-mail]  |
|----|---------|----------|----------|---------------|---------------|------------|---------------|

可能看不到数据库结构，这很令人困惑，但这是我的查询:

It may be confusing without seeing the database structure, but here's my query:

SELECT
  f.id,
  f.firm_name company,
  f.city,
  f.province,

  -- manager
  CONCAT(b.first_name, ' ', b.last_name) manager_name, 
  b.primary_email manager_email

  -- staff
  -- CONCAT(c.first_name, ' ', s.last_name) staff_name,
  -- c.primary_email staff_email

  -- manager (alt attempt)
  -- CASE
  --   WHEN r.user_role_types_id = 7 THEN max(b.primary_email)
  -- END AS manager_email,

  -- staff (alt attempt)
  -- CASE
  --   WHEN r.user_role_types_id = 21 THEN max(b.primary_email)
  -- END AS staff_email

FROM
  projects p1

-- projects
JOIN
(
  SELECT
    id,
    MAX(fiscal_year) max_year, -- latest of the fiscal years
    firms_id
  FROM
    projects
  GROUP BY
    firms_id
) p2
ON p1.fiscal_year = p2.max_year
AND p1.firms_id = p2.firms_id

-- firms
JOIN (
  SELECT
    id,
    firm_name,
    is_cancelled,
    deleted,
    a.city,
    a.province,
  FROM
    firms

    -- address
    JOIN (
      SELECT
        firms_id,
        city,
        province
      FROM
        addresses
    ) a
    ON a.firms_id = id
) f
ON f.id = p1.firms_id

-- roles
JOIN (
  SELECT
    projects_id,
    users_id,
    user_role_types_id
  FROM
    project_user_roles
) r
ON r.projects_id = p1.id

-- managers
JOIN (
  SELECT
    id,
    first_name,
    last_name,
    primary_email
  FROM
    users
) m
ON m.id = r.users_id
-- AND r.user_role_types_id = 7

-- staff
-- JOIN (
--   SELECT
--     id,
--     first_name,
--     last_name,
--     primary_email
--   FROM
--     users
-- ) s
-- ON s.id = r.users_id
-- AND r.user_role_types_id = 21

WHERE
  p1.deleted = 0 -- project not deleted
  AND f.is_cancelled = 0 -- firm not cancelled
  AND f.deleted = 0 -- firm not deleted
  AND
  (
    r.user_role_types_id = 7 -- managers only
    OR r.user_role_types_id = 21 -- staff only
  )

GROUP BY
  f.id

ORDER BY
  f.firm_name

注意:

• 我已经将一些以前的/替代尝试留在了那里(注释掉了)
• 经理和职员都存储在同一表中，具有相同的字段first_name，last_name和primary_email ...将经理与职员分开的唯一内容是他们的r.user_role_types_id，已指定在角色表中
  • 经理= 7，员工= 21
  • I've left some of my previous / alternate attempts in there (commented out)
  • Both managers and staff are stored in the same table with the same fields first_name, last_name and primary_email... The only thing that separates a manager from a staff is their r.user_role_types_id, which is specified in the roles table
    • manager = 7, staff = 21

    在顶部注释掉manager部分，在底部注释GROUP BY部分，并取消注释CASE语句，这是我最近一次尝试获得这两个字段的尝试，但是它产生了如下所示的结果:

    Commenting out the manager section at the top, the GROUP BY section at the bottom, and un-commenting the CASE statements was my latest attempt at getting both of these fields, but it produced results like the following:

    |----|---------|----------|----------|---------------|---------------|
    | id | company | city     | province | manager_email | staff_email   |
    |----|---------|----------|----------|---------------|---------------|
    | 1  | aaa     | toronto  | ON       | [e-mail]  |               |
    | 1  | aaa     | toronto  | ON       |               | [e-mail] |
    | 2  | bbb     | sudbury  | ON       | [e-mail]   |               |
    | 3  | ccc     | hamilton | ON       | [e-mail]  |               |
    | 3  | ccc     | hamilton | ON       |               | [e-mail]  |
    |----|---------|----------|----------|---------------|---------------|
    

    虽然这使我接近了目标，但我仍然需要执行以下操作:

    While this brought me close to my goal, I still needed to do the following:

    • 将重复的行分组为1，同时保留两封电子邮件
      • 这就是让我像以前一样问我以前的问题的原因
      • group duplicate lines into 1, retaining both emails
        • this specifically is what brought me to ask my previous question like I did

        如果需要的话，我很乐意进一步澄清，但是基本上，我只需要能够同时掌握manager和staff的详细信息……有什么想法吗?

        I'll be happy to clarify any of this further if needed, but basically, I just need to be able to grab both the manager and staff details... Any ideas?

        也请让我知道您对重命名/重新措词有任何建议，以提高可见度...很难找到要使用的词.

        Also let me know you have any suggestions on renaming / re-wording this question for better visibility... It was hard to find the words to use.

        推荐答案

        我最终在此处 a>和此处！这是我的新查询，以防万一其他人在这种奇怪的情况下发现自己在这里:

        I ended up figuring it out with help from both here and here! Here's my new query, just in case somebody else in this strange situation finds themselves here:

        SELECT
          f.id,
          f.firm_name company,
          f.city,
          f.province,
          f.region_type region,
          f.industry_type industry,
        
          -- manager
          MAX(
            CASE
              WHEN r.user_role_types_id = 7
              THEN CONCAT(u.first_name, ' ', u.last_name)
            END
          ) AS manager_name,
          MAX(
            CASE
              WHEN r.user_role_types_id = 7
              THEN u.primary_email
            END
          ) AS manager_email,
        
          -- staff
          MAX(
            CASE
              WHEN r.user_role_types_id = 21
              THEN CONCAT(u.first_name, ' ', u.last_name)
            END
          ) AS staff_name,
          MAX(
            CASE
              WHEN r.user_role_types_id = 21
              THEN u.primary_email
            END
          ) AS staff_email
        
        FROM
          projects p1
        
        -- projects
        JOIN
        (
          SELECT
            id,
            MAX(fiscal_year) max_year, -- latest of the fiscal years
            firms_id
          FROM
            projects
          GROUP BY
            firms_id
        ) p2
        ON p1.fiscal_year = p2.max_year
        AND p1.firms_id = p2.firms_id
        
        -- firms
        JOIN (
          SELECT
            id,
            firm_name,
            is_cancelled,
            deleted,
            a.city,
            a.province
          FROM
            firms
        
            -- address
            JOIN (
              SELECT
                firms_id,
                city,
                province
              FROM
                addresses
            ) a
            ON a.firms_id = id
        ) f
        ON f.id = p1.firms_id
        
        -- roles
        JOIN (
          SELECT
            projects_id,
            users_id,
            user_role_types_id
          FROM
            project_user_roles
        ) r
        ON r.projects_id = p1.id
        
        -- users
        JOIN (
          SELECT
            id,
            first_name,
            last_name,
            primary_email
          FROM
            users
        ) u
        ON u.id = r.users_id
        
        WHERE
          p1.deleted = 0 -- project not deleted
          AND f.is_cancelled = 0 -- firm not cancelled
          AND f.deleted = 0 -- firm not deleted
          AND f.industry_type != ''
        
        GROUP BY
          f.id
        
        ORDER BY
          f.firm_name
        
        

        这篇关于MySQL-两次连接同一张表，但结果不同的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！