I have a users table and a campaigns table. I'm trying to show all users where guide = 12481, regardless if they have a response in the campaign table or not. But I'm getting confused with the multiple joins. Can you help?

Thanks!


SELECT
date_format(u.registerDate, '%m-%d-%Y') as registered
,u.uID
,u.firstname
,u.lastname
,date_format(u.last, '%m-%d-%Y') as last
,u.visited
,c.campaign
,c.answer
,date_format(c.answerDate, '%m-%d-%Y') as latestAnswer
FROM
users u
left
JOIN
(SELECT
uID
,MAX(answerDate) mostRecent
FROM
campaigns
WHERE campaign = 'Enrollment'
GROUP
BY
uID) r
ON
u.uID = r.uID
inner
JOIN
campaigns c
ON
r.uID = c.uID
AND
r.mostRecent = c.answerDate
WHERE
u.guide = 12481
ORDER
BY
c.answerDate desc;