why does this straightforward left join query, return null values for strings and dates?

intellisource
intellisource used Ask the Experts™
on
hi everybody,

i am currently having issues with the following left join select. the dateadd and the current timestamp are included in the selection to filter out the results but obviously it would not work as the logic dictates because the tables return null for all joins! attached you would find an xsl workbook with a page for the Faulty Query, as well as the individual tables selecting from.

SELECT 
	CURRENT_TIMESTAMP AS CURDATE, 
	ADDDATE(
		CS.timestamp_SurveyDateCreated, 
		INTERVAL ST.tinyint_ResendInterval DAY
	) AS OFFDATE, 
	C.*, 
	SL.*, 
	SP.* 
FROM 
	21_consumersurveys CS 
LEFT JOIN (
	19_consumers C, 
	25_serviceleads SL, 
	10_serviceprospects SP, 
	20_surveytypes ST 
) ON (
	CS.bigint_ConsumerID = C.bigint_ConsumerID AND 
	CS.bigint_LeadID = SL.bigint_LeadID AND 
	CS.bigint_ReferenceID = SP.bigint_ProspectID AND 
	CS.bigint_SurveyType = ST.bigint_SurveyID
)

Open in new window


the where part of the statement is supposed to look as follows:

WHERE 
	OFFDATE <= CURDATE AND 
	CS.tinyint_SurveyStatus = 0;

Open in new window


please tell me if you can see the issue, i do not seem to find a resolution here...
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Top Expert 2010
Commented:
Check that all the join conditions are correctly linked. you can also try the following:
SELECT
      CURRENT_TIMESTAMP AS CURDATE,
      ADDDATE(
            CS.timestamp_SurveyDateCreated,
            INTERVAL ST.tinyint_ResendInterval DAY
      ) AS OFFDATE,
      C.*,
      SL.*,
      SP.*
FROM
      21_consumersurveys CS
LEFT JOIN 19_consumers C ON CS.bigint_ConsumerID = C.bigint_ConsumerID  
LEFT JOIN 25_serviceleads SL ON CS.bigint_LeadID = SL.bigint_LeadID
LEFT JOIN 10_serviceprospects SP ON CS.bigint_ReferenceID = SP.bigint_ProspectID
LEFT JOIN 20_surveytypes ST ON CS.bigint_SurveyType = ST.bigint_SurveyID
WHERE
      OFFDATE <= CURDATE AND
      CS.tinyint_SurveyStatus = 0;
intellisource

Author

Commented:
Just had to redefine the where bit as follows, but the alternative style LEFT JOIN seems to do the trick ;)
SELECT 
	* 
FROM 
	21_consumersurveys CS 
LEFT JOIN 19_consumers C ON CS.bigint_ConsumerID = C.bigint_ConsumerID 
LEFT JOIN 25_serviceleads SL ON CS.bigint_LeadID = SL.bigint_LeadID 
LEFT JOIN 10_serviceprospects SP ON CS.bigint_ReferenceID = SP.bigint_ProspectID 
LEFT JOIN 20_surveytypes ST ON CS.bigint_SurveyType = ST.bigint_SurveyID 
WHERE 
	ADDDATE(
		CS.timestamp_SurveyDateCreated, 
		INTERVAL ST.tinyint_ResendInterval DAY
	) <= CURRENT_TIMESTAMP AND 
	CS.tinyint_SurveyStatus = 0;

Open in new window

thanks again, om_prakash_p! XD
qasim_md

Commented:
Try this and let me know if it worked ???

SELECT
      CURRENT_TIMESTAMP AS CURDATE,
      ADDDATE(
            CS.timestamp_SurveyDateCreated,
            INTERVAL ST.tinyint_ResendInterval DAY
      ) AS OFFDATE,
      C.*,
      SL.*,
      SP.*
FROM
      21_consumersurveys CS
LEFT JOIN (
      19_consumers C,
      25_serviceleads SL,
      10_serviceprospects SP,
      20_surveytypes ST
) ON (
      CS.bigint_ConsumerID = C.bigint_ConsumerID AND
      CS.bigint_LeadID = SL.bigint_LeadID AND
      CS.bigint_ReferenceID = SP.bigint_ProspectID AND
      CS.bigint_SurveyType = ST.bigint_SurveyID )
WHERE
ADDDATE(
            CS.timestamp_SurveyDateCreated,
            INTERVAL ST.tinyint_ResendInterval DAY
      ) <= CURRENT_TIMESTAMP AND
      CS.tinyint_SurveyStatus = 0;

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial