Return next row that meets condition

Question

I can't find a solution to the following problem. Assuming I have 2 tables:

Table 1:

Name        | Age      
Nick        | 52      
Adam        | 52      
Monica      | 40

Table 2:

Name        | Task       | Value  | Done
Nick        | Clean      | 010000 | Y
Nick        |            | 011000 |
Nick        |            | 020000 | 
Nick        | Vacuum     | 025000 | 
Nick        | Do Dishes  | 030000 | 
Adam        |            | 010000 |
Adam        | Clean      | 015000 | Y
Adam        | Dust       | 030000 | Y
Adam        | Cook       | 035000 | 
Monica      | PaintHouse | 010000 |
Monica      | Garbage    | 020000 |

What I'm looking for is this:

How can I write an SQL that gives me the result of all everyone that is 52 and shows the next task he/she has to do, skipping all the empty entries in "task" Output should look something like:

Name        | Age     | Task 
Nick        | 52      | Vacuum
Adam        | 52      | Cook

Database is 2000 version of MS SQL Server. Also, I don't have any write access, only read access.

UPDATE: In the original post, I posted:

Adam        | Cook       | 030000

This was not correct It should have been:

Adam        | Cook       | 035000

I have corrected this error.

Answer 1

This answer returns your desired result and it should run on SQL-Server 2000, but it is not valid until you are not able to set a Task order.

SELECT t1.Name, t1.Age,
       (SELECT TOP 1 t2.Task
        FROM   t2
        WHERE  t2.Name = t1.Name 
        AND    t2.Task IS NOT NULL
        AND    (t2.Done IS NULL OR t2.Done <> 'Y')) Task
FROM   t1
WHERE  t1.Age = 52
GO

Name | Age | Task  
:--- | --: | :-----
Nick |  52 | Vacuum
Adam |  52 | Cook  

dbfiddle here

UPDATE

According to the dbfiddle supplied by the OP:

SELECT t1.P_No,
       ISNULL((SELECT   TOP 1 t2.Task
               FROM     t2
               WHERE    t2.P_No = t1.P_No 
               AND      t2.Task <> ''
               AND      t2.No <> '0'
               ORDER BY t2.P_No, t2.Value), '') AS Task
FROM   t1
WHERE  t1.Status <> 3
GO

P_No | Task     
:--- | :--------
P1   |          
P2   | Do Dishes
P4   |          
P6   |          
P8   |          
P10  | Do Dishes

dbfiddle here

Answer 2

The solution is to use outer apply which will cross reference the table Table02 with Name and order by Value ASC showing top (1) rows.

drop table if exists dbo.Table01;
drop table if exists dbo.Table02;

create table dbo.Table01 (
    Name varchar(100)
    , age int
);

create table dbo.Table02 (
    Name varchar(100)
    , Task varchar(100)
    , Value varchar(100)
    , Done char(1)
);


insert into dbo.Table01 (Name, Age)
values ('Nick', 52), ('Adam', 52), ('Monica', 40);

insert into dbo.Table02 (Name, Task, Value, Done)
values ('Nick', 'clean', '010000', 'Y')
    , ('Nick', '', '011000', '')
    , ('Nick', '', '020000', '')
    , ('Nick', 'Vacuum', '025000', '')
    , ('Nick', 'Do Dishes', '030000', '')
    , ('Adam', '', '010000', '')
    , ('Adam', 'Clean', '015000', 'Y')
    , ('Adam', 'Dust', '030000', 'Y')
    , ('Adam', 'Cook', '030000', '')
    , ('Monica', 'PaintHouse', '010000', '')
    , ('Monica', 'Garbage', '020000', '');

select
*
from dbo.Table01 t01
    outer apply (
            select
                top (1)
                t02.Task
            from dbo.Table02 t02
            where t01.Name = t02.Name
                and isnull(t02.Done, '') <> 'Y'
                and isnull(t02.Task, '') <> ''
            order by t02.Value asc
    ) tn
where t01.age = 52

Answer 3

This should work in 2000, too:

select
   *,
   (select top 1 Task 
    from Table02 as t2
    where t1.name = t2.name -- correlate both tables
      and t2.Task <> ''
      and Done <> 'Y'  -- ?? seems to be what you want
    order by Task      -- ?? whatever you need to determine the "next" task (maybe no order at all)
   )
from Table01 as t1
where age = 52