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.
Thanks!
SELECT date_format(u.registerDate, '%m-%d-%Y') as registerDate
, u.uID
, u.firstName
, u.lastName
, u.memberType
, u.logins
, date_format(u.lastLogin, '%m-%d-%Y') as lastLoginFormatted
, date_format(c.latest, '%m-%d-%Y') as answerDate
, c.campaign
, c.answer
FROM users u
LEFT
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
, u.uID
, u.firstName
, u.lastName
, u.memberType
, u.logins
, DATE_FORMAT(u.lastLogin, '%m-%d-%Y') AS lastLoginFormatted
, DATE_FORMAT(c.latest, '%m-%d-%Y') AS answerDate
, c.campaign
, c.answer
FROM users AS u
LEFT
JOIN ( SELECT uID
, MAX(answerDate) AS latest
FROM campaigns
WHERE campaign = 'Enrollment'
AND answer <> 'Rejected'
GROUP
BY uID ) AS c
ON c.uID = u.uID
LEFT OUTER
JOIN campaigns
ON campaigns.uID = c.uID
AND campaigns.answerDate = c.latest
WHERE u.sponsorID = '110'
AND u.memberType = 'No'
AND u.lastName = 'Laib'
ORDER
BY lastLogin DESC;
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
FROM campaigns
WHERE campaign = 'Enrollment' AND answer <> 'Rejected' -- believe this will exclude if 'answer' is null
GROUP BY uID
) AS c