> DDL Below..
>
> Using that DDL you will end up with a TestCases Table that will have
> the
> following data
> 1 1 13080 Windows XP Card1 Games 1 NO
> 2 1 13080 Windows XP Card2 Games 1 NO
> 3 1 13080 Windows 2000 Card1 Games 1 NO
> 4 1 13080 Windows 2000 Card2 Games 1 NO
> And a Reports table with the data of..
>
> 1 1 Fail 2006-06-22 13:26:25.193
> 2 1 Pass 2006-07-01 13:26:25.193
> 3 2 Pass 2006-08-01 13:26:25.193
> What I need is a list of all the TestCases w/their most recent
> results, but
> also including the ones w/o a result.
> They query I am futzing with is..
>
> SELECT rtc.ID As TestCaseID,
> rt.TestCase,
> rtc.OS,
> rtc.Card,
> r.Result,
> r.ReportDate
> FROM Reports r
> LEFT OUTER JOIN TestCases rtc ON r.TestCaseID = rtc.ID
> LEFT OUTER JOIN Tests rt ON rtc.TestID = rt.ID
> WHERE r.ReportDate IN
> (
> SELECT MAX(r.ReportDate)
> FROM TestCases tc
> LEFT OUTER JOIN Reports r ON r.TestCaseID = tc.ID
> WHERE (
> (   Project = '1')
> AND tc.Untestable = 'NO'
> AND tc.ID = rtc.ID
> )
> )
>
> ORDER BY TestCase
>
> That will return the list of most recent results for each TestCase but
> not
> including any that don't have a report table entry...
> 1 Test 1 Windows XP Card1 Pass 2006-07-01 13:26:25.193
> 2 Test 1 Windows XP Card2 Pass 2006-08-01 13:26:25.193
> What I need is something like..
>
> 1 Test 1 Windows XP Card1 Pass 2006-07-01 13:26:25.193
> 2 Test 1 Windows XP Card2 Pass 2006-08-01 13:26:25.193
> 3 Test 1 Windows 2000 Card1 NULL NULL
> 4 Test 1 Windows 2000 Card2 NULL NULL
> ..but I don't know how to change to query to return such results.
>
> ---------------------------------------------------------------------
> ------------------ CREATE TABLE Tests ( ID varchar(36), TestCase
> varchar (512), AppID int, Type varchar (32), PRIMARY KEY (ID) ) CREATE
> TABLE TestCases ( ID varchar(36), AppID int, TestID, varchar (36), OS
> varchar (32), Card varchar (16), Type varchar (32), Project smallint,
> Untestable varchar (3) PRIMARY KEY (ID) ) CREATE TABLE Reports ( ID
> int, TestCaseID varchar (36), Result varchar(4), ReportDate datetime
> PRIMARY KEY (ID) ) INSERT INTO [Tests]
> ([ID],[TestCase],[AppID],[Type])VALUES('13080','Test 1',512,'PC
> Desktop') INSERT INTO [TestCases]
> ([ID],[AppID],[TestID],[OS],[Card],[Type],[Project],[Untestable])VALUE
> S('1','1','13080','Windows XP','Card1','Games','1','NO') INSERT INTO
> [TestCases]
> ([ID],[AppID],[TestID],[OS],[Card],[Type],[Project],[Untestable])VALUE
> S'2','1','13080','Windows XP','Card2','Games','1','NO') INSERT INTO
> [TestCases]
> ([ID],[AppID],[TestID],[OS],[Card],[Type],[Project],[Untestable])VALUE
> S('3','1','13080','Windows 2000','Card1','Games','1','NO') INSERT INTO
> [TestCases]
> ([ID],[AppID],[TestID],[OS],[Card],[Type],[Project],[Untestable])VALUE
> S('4','1','13080','Windows 2000','Card2','Games','1','NO') INSERT INTO
> [Reports] ([ID],[TestCaseID],[Result],[ReportDate])
> VALUES(3,'2','Pass','Aug 1 2006  1:26:25:193PM') INSERT INTO [Reports]
> ([ID],[TestCaseID],[Result],[ReportDate]) VALUES(2,'2','Pass','Jul 1
> 2006  1:26:25:193PM') INSERT INTO [Reports]
> ([ID],[TestCaseID],[Result],[ReportDate]) VALUES(1,'1','Pass','Jun 22
> 2006  1:26:25:193PM')
>