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
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
try
SELECT Guilds.id
, Guilds.guild
, chas.challenger
, chal.challengee
FROM Guilds
LEFT OUTER
JOIN Challenges as chal
ON chal.challengee = Guilds.id as xxx_challengee
LEFT OUTER
JOIN Challenges as chas
ON chas.challenger = Guilds.id as xxx_challenger
ORDER
BY Guilds.id
not sure if it will work