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;
how kind of you to ask for me by name, but i'm sure there are others out there that could've done this one as well
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 AS u
JOIN ( SELECT uID
, MAX(answerDate) AS latest
WHERE campaign = 'Enrollment'
AND answer <> 'Rejected'
BY uID ) AS c
ON c.uID = u.uID
ON campaigns.uID = c.uID
AND campaigns.answerDate = c.latest
WHERE u.sponsorID = '110'
AND u.memberType = 'No'
AND u.lastName = 'Laib'
BY lastLogin DESC;
I'm now getting the following error:
Unknown column 'c.campaign' in 'field list'
oops, sorry, c.campaign and c.answer in the SELECT clause should be campaign.campaign and campaign.answer, although to be honest you don't need to return campaign.campaign because it's gonna say 'Enrollment'
Question: Do you want to be including nulls in the sub query?
LEFT JOIN (
SELECT uID , MAX(answerDate) AS latest
WHERE campaign = 'Enrollment' AND answer <> 'Rejected' -- believe this will exclude if 'answer' is null
GROUP BY uID
) AS c
I do not want to include nulls in this case. Thank you for following up.
This topic is now closed. New replies are no longer allowed.