Join is not producing the right results
Can you help me with this query? It's pulling the most recent answer date in the campaigns table for each user, but not the correct associated answer.
SELECT date_format(u.registerDate, '%m-%d-%Y') as registerDate
, date_format(u.lastLogin, '%m-%d-%Y') as lastLoginFormatted
, date_format(c.latest, '%m-%d-%Y') as answerDate
FROM users u
JOIN (SELECT uID, MAX(answerDate) AS latest, campaign, answer FROM campaigns where campaign = 'Enrollment' GROUP BY uID) AS c
ON c.uID = u.uID
where u.sponsorID = '110'
and u.memberType = 'No'
and answer != 'Rejected'
and u.lastName = 'Laib'
order by lastLogin desc;