Friday, March 23, 2012

Help with SELECT query

Hi

Can someone help me with the following select query as it doesn't seem to bring anything back in the Query Analyzer. I've tried two different methods, but get nothing.

1st Method

USE EmployerEngagement

SELECT On_Stop
FROM tblEmployer
WHERE On_Stop = NULL AND Emp_ID IN
(SELECT Emp_ID
FROM tblWP_Details
WHERE Record_ID IN
(SELECT Record_ID
FROM tblVetting
WHERE Next_Vett_Date < GETDATE()))

2nd Method

USE EmployerEngagement

SELECT On_Stop
FROM tblEmployer INNER JOIN tblWP_Details
ON tblEmployer.Emp_ID = tblWP_Details.Emp_ID INNER JOIN dbo.tblVetting
ON tblWP_Details.Record_ID = tblVetting.Record_ID
WHERE tblEmployer.On_Stop = NULL
AND tblVetting.Next_Vett_Date < GETDATE()

Thankssee "SET ANSI_NULLS" in books online...

change "where ... = NULL" to "where ... IS NULL"

and see how you get on..

HTH,

Bill|||Thanks, I was just about to reply to say I've figured it out.

I just realised that the field in question had a default value of 0 not Null. :rolleyes:|||but did you get Bill's point about = Null. Nothing equals Null because Null is not a value. it is unknown or the abscence of value. So unless you play with the SQL Server settings, anytime you search for

SELECT * FROM MyTable WHERE MyField = Null

you will always get 0 rows.

No comments:

Post a Comment