mysql - 根据结果​​从查询返回值

Question

我正在尝试构建此查询来显示存储表的特定信息。我还需要查询来显示敌人公会名称，但我无法让查询获取敌人公会 ID 并将其链接到名称。

SELECT g.wPos as wPos, g.szGuildName as szGuildName, g.dwGuildExpWeek as dwGuildExpWeek, g.dwEnemyGuildID as dwEnemyGuildID, gm.wPower as wPower, gd.szName as szName
FROM guild as g
LEFT JOIN guild_member AS gm ON gm.dwGuildID = g.dwGuildID AND gm.wPower = '1'
LEFT JOIN gamedata AS gd ON gd.dwID = gm.dwRoleID
WHERE g.wPos = '1'

现在查询的输出结果如下: Query Results Currently

我现在需要它做的是获取它找到的 dwEnemyGuildID，然后使用该 ID 搜索 szGuildName，同时显示它找到的其他数据。

Answer 1

使用SELF JOIN的概念，其中如果我们有一个引用同一个表的字段，我们将再次连接同一个表。这里dwEnemyGuildID是对同一个表的引用。

一个简单的例子是从 employees 表中查找员工的经理。

引用:Find the employee id, name along with their manager_id and name

SELECT 
    g.wPos as wPos, 
    g.szGuildName as szGuildName, 
    g.dwGuildExpWeek as dwGuildExpWeek, 
    g.dwEnemyGuildID as dwEnemyGuildID, 
    enemy_g.szGuildName as szEnemyGuildName,  -- pick name from self joined table
    gm.wPower as wPower, 
    gd.szName as szName
FROM guild as g
LEFT JOIN guild_member AS gm ON gm.dwGuildID = g.dwGuildID AND gm.wPower = '1'
LEFT JOIN guild AS enemy_g ON g.dwEnemyGuildID = enemy_g.dwGuildID   -- Use self join
LEFT JOIN gamedata AS gd ON gd.dwID = gm.dwRoleID
WHERE g.wPos = '1';