SQL Query Conditional Select based on Newer Entry

I have a query which searches a database of tests for failed tests and returns a certain code if there is a failure for a specific set of tests. What I need for it to do, is to only return that code if there is not a newer version of that test saved in the database that has passed. This is part of a set of queries that, given a certain project number, query every subset of that number (there are 5 test subsets), and then check and give it one of 4 different statuses. Here is an excerpt:

SELECT
MAX(
CASE
WHEN PROJECT.PARENT_ID IN
(SELECT parent_id FROM object WHERE object.id IN
(SELECT parent_id FROM object WHERE object.id IN
(SELECT parent_id FROM TESTRESULTS WHERE TESTRESULTS.RESULTID = '962'))) 
AND PROJECT.PROJECTNAME LIKE '%EMC%' 
THEN '4'
ELSE.....

I need to check the same table for newer entries with the same name, that belong to the same project, that have passed their tests instead of failed, ie RESULTID = '961', and instead let it fall through to the other cases, rather than assign it a '4'. The dates of the tests are stored in the table. I've played with this a while on my own, doing some joins from the table back onto itself, but this parent's parent's parent's hierarchy is making it difficult to say the least. Also, I did not design this database and don't have the ability to alter the schema in any way.

Edit: This is the relevant structure of the table, and some example records:

ID | PARENT_ID | TEST TYPE | RESULTID | TESTDATE
-------------------------------------------------
4  |     1     | ESD       |   962    | 01-01-11
5  |     2     | CI        |   961    | 01-02-11
6  |     3     | ESD       |   961    | 02-01-11
7  |     8     | RI        |   962    | 02-02-11

I need a query that will return开发者_JAVA百科 tests that fail (resultid 962) unless there is another test with the same name, and the same parent_id relationship described in the query above, but that passed the test (resultid 961). So, if I ran the query on 01-01-11, I would get the row with ID of 4, but if I ran it on 02-01-11, it would return no rows, and on 02-02-11 it would return the row with ID of 7. Also, there are resultids > 962 for anomalies, etc. and a resultid < 961 for tests that haven't taken place yet.

Edit again: Object Table structure

ID | PARENT_ID | Description   |
--------------------------------
50 |     0     | Product Level | (Very Top)
51 |    50     | Project Level | (The PROJECT table describes this lvl)
52 |    51     | Work Request  |
1  |    52     | Work Order    |
2  |    52     | Work Order    |
3  |    52     | Work Order    |
8  |    52     | Work Order    |

So, referencing the table above, each test in the TESTRESULT table is a child of a worker, which is in turn the child of a work request, which is the child of a project, which is the child of a product. I need to be able to tie every individual test result back to the project level, which is why I am running everything up to the PROJECT table, rather than the work request table.

---

I rewrote the query (for reference, the original answer is below).

WHEN PROJECT.PARENT_ID IN
(SELECT parent_id FROM object WHERE object.id IN
(SELECT parent_id FROM object WHERE object.id IN
(SELECT parent_id FROM TESTRESULTS t1 WHERE t1.RESULTID = '962'
and not exists 
(select * from object o2 
WHERE PROJECT.PARENT_ID = o2.id and o2.id IN
(SELECT parent_id FROM object WHERE object.id IN
(SELECT parent_id FROM object WHERE object.id IN
(SELECT parent_id FROM TESTRESULTS t2 WHERE t2.RESULTID = '961'
and t1.testtype = t2.testtype and t2.testdate > t1.testdate)))))))
AND PROJECT.PROJECTNAME LIKE '%EMC%' 

Test scenario to reach that result:

create table #tmpObj (ident int primary key, parent_id int, description varchar(20));
insert into #tmpObj (ident, parent_id, description) values (50, 0, 'Product Level');
insert into #tmpObj (ident, parent_id, description) values (60, 50, 'Project X');
insert into #tmpObj (ident, parent_id, description) values (70, 60, 'Work request 1');
insert into #tmpObj (ident, parent_id, description) values (101, 70, 'Work order 1a');
insert into #tmpObj (ident, parent_id, description) values (102, 70, 'Work order 1b');
insert into #tmpObj (ident, parent_id, description) values (103, 70, 'Work order 1c');
insert into #tmpObj (ident, parent_id, description) values (71, 60, 'Work request 2');
insert into #tmpObj (ident, parent_id, description) values (111, 71, 'Work order 2a');
insert into #tmpObj (ident, parent_id, description) values (112, 71, 'Work order 2b');
insert into #tmpObj (ident, parent_id, description) values (113, 71, 'Work order 2c');
insert into #tmpObj (ident, parent_id, description) values (61, 50, 'Project Z');
insert into #tmpObj (ident, parent_id, description) values (80, 61, 'Work request 1');
insert into #tmpObj (ident, parent_id, description) values (201, 80, 'Work order 1a');
insert into #tmpObj (ident, parent_id, description) values (202, 80, 'Work order 1b');
insert into #tmpObj (ident, parent_id, description) values (203, 80, 'Work order 1c');
insert into #tmpObj (ident, parent_id, description) values (81, 61, 'Work request 2');
insert into #tmpObj (ident, parent_id, description) values (211, 81, 'Work order 2a');
insert into #tmpObj (ident, parent_id, description) values (212, 81, 'Work order 2b');
insert into #tmpObj (ident, parent_id, description) values (213, 81, 'Work order 2c');

create table #tmpTest (ident int primary key, parent_id int, name1 varchar(10), resultid int, testdate date);
insert into #tmpTest (ident, parent_id, name1, resultid, testdate) values (2001, 101, 'ESD', 962, '2011-01-01');
insert into #tmpTest (ident, parent_id, name1, resultid, testdate) values (2002, 102, 'CI', 961, '2011-01-02');
insert into #tmpTest (ident, parent_id, name1, resultid, testdate) values (2003, 103, 'RI', 961, '2011-01-02');
insert into #tmpTest (ident, parent_id, name1, resultid, testdate) values (2004, 111, 'ESD', 961, '2011-02-01');
insert into #tmpTest (ident, parent_id, name1, resultid, testdate) values (2005, 112, 'CI', 962, '2011-02-02');
insert into #tmpTest (ident, parent_id, name1, resultid, testdate) values (2006, 113, 'RI', 961, '2011-02-03');
insert into #tmpTest (ident, parent_id, name1, resultid, testdate) values (2007, 201, 'ESD', 961, '2011-01-01');
insert into #tmpTest (ident, parent_id, name1, resultid, testdate) values (2008, 202, 'CI', 961, '2011-01-02');
insert into #tmpTest (ident, parent_id, name1, resultid, testdate) values (2009, 203, 'RI', 961, '2011-01-02');

SELECT * FROM #tmpObj o1 WHERE o1.ident IN
(SELECT parent_id FROM #tmpObj WHERE #tmpObj.ident IN
(SELECT parent_id FROM #tmpObj WHERE #tmpObj.ident IN
(SELECT parent_id FROM #tmpTest t1 WHERE t1.RESULTID = 962
and not exists 
(select * from #tmpObj o2 
WHERE o1.ident = o2.ident and o2.ident IN
(SELECT parent_id FROM #tmpObj WHERE #tmpObj.ident IN
(SELECT parent_id FROM #tmpObj WHERE #tmpObj.ident IN
(SELECT parent_id FROM #tmpTest t2 WHERE t2.RESULTID = 961
and t1.name1 = t2.name1 and t2.testdate > t1.testdate))))))); 

---

Original answer:

Well, the given information confuses me a little, but it seems as if this calls for an EXISTS() subquery (I hope that is available in interbase), like the following (simplified, without the parent-parent stuff) example:

create table #tmpTest (ident int primary key, parent_id int, name1 varchar(10), resultid int, testdate date);
insert into #tmpTest (ident, parent_id, name1, resultid, testdate) values (4, 1, 'ESD', 962, '2011-01-01');
insert into #tmpTest (ident, parent_id, name1, resultid, testdate) values (5, 2, 'CI', 961, '2011-01-02');
insert into #tmpTest (ident, parent_id, name1, resultid, testdate) values (6, 1, 'ESD', 961, '2011-02-01');
insert into #tmpTest (ident, parent_id, name1, resultid, testdate) values (7, 8, 'RI', 962, '2011-02-02');
insert into #tmpTest (ident, parent_id, name1, resultid, testdate) values (9, 8, 'RI', 962, '2011-02-03');

select * from #tmpTest t1 
where resultid = 962 and not exists 
(select ident from #tmpTest t2 
where t2.parent_id = t1.parent_id 
and t2.resultid = 961 
and t2.testdate > t1.testdate); 

I assumed that the parent_id in your testresults table can be used to identify the test category, but that may not be accurate. I hope this will at least help you to refine your query.