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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.