Join is not producing the right results

[busboy]

Rudy,

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;

[r937]

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;

[busboy]

I'm now getting the following error:

Unknown column 'c.campaign' in 'field list'

[r937]

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'

[K. Wolfe]

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'

UH OH!

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

[busboy]

Thanks Rudy!!

[busboy]

I do not want to include nulls in this case. Thank you for following up.