busboy — 2012-10-26T21:33:28-04:00 — #1
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;
r937 — 2012-10-27T13:46:27-04:00 — #2
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;
busboy — 2012-10-27T15:43:10-04:00 — #3
I'm now getting the following error:
Unknown column 'c.campaign' in 'field list'
r937 — 2012-10-27T16:04:43-04:00 — #4
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'
busboy — 2012-10-29T12:42:09-04:00 — #5
kylewolfe — 2012-10-29T16:20:32-04:00 — #6
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
busboy — 2012-10-29T16:54:32-04:00 — #7
I do not want to include nulls in this case. Thank you for following up.
system — 2014-10-07T23:08:01-04:00 — #8
This topic is now closed. New replies are no longer allowed.