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