We help IT Professionals succeed at work.

Return a result if Query brings back nothing

Hi Everyone,

I have a query that I am running that I would like to understand how to return a result if the query brings back nothing.

At the moment it looks like this ...

SELECT ISNULL(MilestoneDate,GETDATE())
FROM WF_Milestones
WHERE EFOLDERID = '123456'AND ID = ('123' - 1)

What I am doing here is querying the ID column from the previous record to bring back a date, if this has null in the date column then it brings back the current date. This works as I want it to.

However if the record I am querying is the first in the table or recordset, then it does not return anything. How can I make sure it always brings back a result, preferably the current date?

Thanks in advance everyone!

Comment
Watch Question

Adam MenkesC# ASP.NET Developer
Top Expert 2010

Commented:
SELECT TOP 1 ISNULL(MilestoneDate,GETDATE())
FROM WF_Milestones
WHERE EFOLDERID = '123456'AND ID = ('123' - 1)
UNION ALL
SELECT GETDATE()

Commented:
>What I am doing here is querying the ID column from the previous record

Something is lost in translation as the SQL shown does not appear to do that.  Please give a small amount of sample data, and the results you want to get back.

Adam MenkesC# ASP.NET Developer
Top Expert 2010

Commented:
declare @WF_Milestones table (
  id int ,
  EFOLDERID varchar(10),
  MilestoneDate datetime
)

INSERT INTO @WF_Milestones
select 122, '123456', NULL
SELECT TOP 1 x.MyDate FROM
(SELECT MyDate = ISNULL(MilestoneDate, GETDATE()) FROM @WF_Milestones
WHERE EFOLDERID = '123456' AND ID = (123 - 1)
UNION ALL
SELECT MyDate = GETDATE()
) x
C# ASP.NET Developer
Top Expert 2010
Commented:
Here is a sample where it will find it, and won't find it.



declare @WF_Milestones table (
  id int ,
  EFOLDERID varchar(10),
  MilestoneDate datetime
)

INSERT INTO @WF_Milestones
select 1, '99999', NULL

SELECT TOP 1 x.MyDate FROM 
(SELECT MyDate = ISNULL(MilestoneDate, GETDATE()) FROM @WF_Milestones
WHERE EFOLDERID = '123456' AND ID = (123 - 1)
UNION ALL 
SELECT MyDate = GETDATE()
) x


INSERT INTO @WF_Milestones
select 122, '123456', DATEADD(D, -100, GETDATE())

select * from @WF_Milestones

SELECT top 1 x.MyDate FROM 
(SELECT id, MyDate = ISNULL(MilestoneDate, GETDATE()) FROM @WF_Milestones
WHERE EFOLDERID = '123456' AND ID = (123 - 1)
UNION ALL 
SELECT null, MyDate = GETDATE()
) x
ORDER by x.id desc

Open in new window

Results.PNG