Rows only show for those who are in the campaigns table

[busboy]

I’m not entirely sure what happened to my query. This is one that I haven’t touched in a very long time. It used to show all members that have a sponsor of 333, and also their answer to the enrollment question if they have answered the campaign in the past. Now it is only return results if the member has indeed answered the question.

Any clues on what I’m overlooking?

Thanks!

SELECT 
	DATE_FORMAT(m.registerDate, '%m-%d-%Y') AS registerDate,
	m.memberID,
	m.firstName,
	m.lastName,
	m.memberType,
	m.totalLogins,
	DATE_FORMAT(m.lastLogin, '%m-%d-%Y') AS lastLoginFormatted,
	DATE_FORMAT(c.latest, '%m-%d-%Y') AS answerDate,
	campaigns.answer
FROM members AS m
LEFT  
  JOIN ( SELECT memberID
			  , MAX(answerDate) AS latest
		   FROM campaigns 
		  WHERE type = 'Enrollment'
		 GROUP 
			 BY memberID ) AS c
	ON c.memberID = m.memberID
LEFT OUTER
  JOIN campaigns
	ON campaigns.memberID = c.memberID
   AND campaigns.answerDate = c.latest
 WHERE m.sponsorID = 333
   AND (m.memberType = 'Lead' or m.memberType = 'Competitor')
   AND campaigns.status != 'Rejected'
   AND m.sponsorCheck in ('Confirmed', 'Assigned')
ORDER BY
	lastLogin desc;

[r937]

as you know, in a LEFT OUTER JOIN, whenever there is a row from the left table which has no match in the right table, all columns from the right table in the result row of the join are set to NULL

so when you have “c LEFT OUTER JOIN campaigns” you will get NULLs in all campaigns columns in the results whenever there is no matching campaigns row (based on the join conditions)

but then you throw a spanner in the works by adding this WHERE condition –

AND campaigns.status != 'Rejected'

as you must know, NULL will never be equal to anything (even another NULL), and, more significantly for this case, will never ~not~ be equal to something like ‘Rejected’

therefore the WHERE condition fails, and all unmatched rows are discarded, in effect making your LEFT OUTER JOIN behave as an inner join

make sense?

move that condition to the ON clause of the join

[busboy]

Praise the Lord for good men like you r937! You always are able to see the things that I overlook! The query works again as it should. Apparently I made a change in the past that broke this. No telling how long ago. Ugh.