how do i cancel out the rows with null fields returned in this mysql query?

hi.
i need to adapt the following query so that i do not get results with null fields.
SELECT 
	sq.bigint_SurveyID, 
	sq.timestamp_SurveyTrigger, 
	sq.tinyint_Initiated, 
	IFNULL(sl.bigint_LeadID,0) AS bigint_LeadID, 
	IFNULL(sp.bigint_ProspectID,0) AS bigint_ProspectID, 
	cs.bigint_ConsumerID, 
	cs.`text_ConsumerE-Mail`, 
	cs.text_ConsumerName 
FROM 
	26_surveyqueue sq 
LEFT JOIN 25_serviceleads sl ON sq.bigint_LeadID = sl.bigint_LeadID 
LEFT JOIN 10_serviceprospects sp ON sq.bigint_ReferenceID = sp.bigint_ProspectID 
LEFT JOIN 19_consumers cs ON sl.bigint_ConsumerID = cs.bigint_ConsumerID 
WHERE 
	sq.timestamp_SurveyTrigger <= "2012-01-04 00:00:00" AND 
	sq.tinyint_Initiated = 0 
ORDER BY 
	sq.timestamp_SurveyTrigger ASC, 
	sq.bigint_SurveyID ASC;

Open in new window

this returns the resultset displayed in the following csv
query-results.csv
intellisourceAsked:
Who is Participating?
 
Pratima PharandeCommented:
SELECT
      sq.bigint_SurveyID,
      sq.timestamp_SurveyTrigger,
      sq.tinyint_Initiated,
      IFNULL(sl.bigint_LeadID,0) AS bigint_LeadID,
      IFNULL(sp.bigint_ProspectID,0) AS bigint_ProspectID,
      cs.bigint_ConsumerID,
      cs.`text_ConsumerE-Mail`,
      cs.text_ConsumerName
FROM
      26_surveyqueue sq
LEFT JOIN 25_serviceleads sl ON sq.bigint_LeadID = sl.bigint_LeadID
LEFT JOIN 10_serviceprospects sp ON sq.bigint_ReferenceID = sp.bigint_ProspectID
inner JOIN 19_consumers cs ON sl.bigint_ConsumerID = cs.bigint_ConsumerID
WHERE
      sq.timestamp_SurveyTrigger <= "2012-01-04 00:00:00" AND
      sq.tinyint_Initiated = 0
ORDER BY
      sq.timestamp_SurveyTrigger ASC,
      sq.bigint_SurveyID ASC;

0
 
intellisourceAuthor Commented:
hi pratima_mcs,
been resolving on my own a bit and i found that the following query also resolves the issue ;)
SELECT 
	sq.bigint_SurveyID, 
	sq.timestamp_SurveyTrigger, 
	sq.tinyint_Initiated, 
	IFNULL(sl.bigint_LeadID,0) AS bigint_LeadID, 
	IFNULL(sp.bigint_ProspectID,0) AS bigint_ProspectID, 
	cs.bigint_ConsumerID, 
	cs.`text_ConsumerE-Mail`, 
	cs.text_ConsumerName 
FROM 
	26_surveyqueue sq 
LEFT JOIN 25_serviceleads sl ON sq.bigint_LeadID = sl.bigint_LeadID 
LEFT JOIN 10_serviceprospects sp ON sq.bigint_ReferenceID = sp.bigint_ProspectID 
LEFT JOIN 19_consumers cs ON sl.bigint_ConsumerID = cs.bigint_ConsumerID 
WHERE 
	sq.timestamp_SurveyTrigger <= "2012-01-04 00:00:00" AND 
	sq.tinyint_Initiated = 0 AND 
	cs.bigint_ConsumerID <> 0 
ORDER BY 
	sq.timestamp_SurveyTrigger ASC, 
	sq.bigint_SurveyID ASC;

Open in new window

but due to the INNER JOIN IN your query, i believe i shall accept your's as the solution ;)
0
 
intellisourceAuthor Commented:
thanks! was just not that aware of the inner join's purpose hehe ;)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.