正确构建我的MySQL JOIN查询

Question

I have 2 tables from which I'm trying to pull data from together in 1 query.

Guilds:
id (int) | guild (varchar)

Challenges:
id (int) | challenger (int) | challengee (int)

The "challenger" and "challengee" fields reference a "id" from the Guilds table. Using one query, I'd like to pull the Guild field for both the "challenger" and "challengee" (based on the "guild id"), but am stuck on the correct syntax to use.

SELECT challenges.`id` AS c_id, challenges.`challengee` AS c_challengee, challenges.`challenger` AS c_challenger, guilds.`guild`
FROM challenges
LEFT JOIN guilds
ON challenges.`challengee` = guilds.`id`

Is it possible building a query that would grab both the "challenger" and "challengee" Guild (field)?

An example of the result I'm trying to achieve:

challenge_id  |  challenger  |  challenger_guild  |  challengee  |  challengee_guild
-------------   -------------  ------------------  --------------  -----------------
2             |      8       |  oBsolete          |      5       |  Plague

Answer 1

Here you go:

SELECT t.id, t.challenger, t.g_challenger, t.challengee, g2.guild as g_challengee
FROM (
  SELECT c1.id, c1.challenger, g1.guild as g_challenger, c1.challengee
  FROM Guilds g1
  JOIN Challenges c1
  ON g1.id = c1.challenger
) t 
JOIN Guilds g2
ON g2.id = t.challengee

Working Fiddle: http://sqlfiddle.com/#!2/e036d0/18