quote:

---

Originally posted by jsmith8858

you need a CROSS JOIN. see if this helps:

http://weblogs.sqlteam.com/jeffs/archive/2005/09/12/7755.aspx

---

• I'm querying a single table - TestResults. While I do have a TestCases table and a Platforms table, a cross join of those would A. Yield no test results (because those are stored only in the TestResults table) and B. Would yield invalid test case:platform combinations unless I use a WHERE clause to narrow down platforms appropriately.
• Even if I can get the right combinations of CROSS and LEFT/RIGHT/INNER/OUTER to return something for each test case:platform combination, I actually have multiple records for many of the combinations. (E.g. the test case "1101-Installation" has been tested on XPP-2 (XP Pro, SP2) on virtually every build since the start of system test.) Still, I guess this could make a good subquery and I could use MAX(TestDate) and MAX(Build) to return the recordset I need.

quote:

---

Originally posted by cmdr_skywalker

Please specify the tables, columns, sample data.

---

SELECT ISNULL(oj.Result, 'UNTESTED') AS Result, ISNULL(tc.Number, 'UNTESTED') AS TCnum, ISNULL(p.OS, 'UNTESTED') AS OS, 
              oj.Build, oj.ID, oj.TestDate, oj.Engineer, oj.FullName AS TestCase, oj.Weight, p.OSbitmask
FROM  TestCases tc CROSS JOIN Platforms p LEFT OUTER JOIN
      (SELECT tr.TestCase, tr.OS, tr.Build, tr.ID, tr.TestDate, tr.Engineer, tr.Result, tr.TR, tr.Project, 
	          tc.FullName, fc.Weight
       FROM TestResults tr INNER JOIN
            TestCases tc ON tr.TestCase = tc.Number AND tr.Project = tc.Project INNER JOIN
            FuncComps fc ON LEFT(tr.TestCase, 2) = fc.Number
       WHERE (tr.Project = 'av09120000')) oj ON tc.Project = oj.Project AND tc.Number = oj.TestCase AND p.OS = oj.OS
WHERE (tc.Project = 'av09120000') AND (p.OSbitmask IN (1, 16, 64))
ORDER BY oj.Weight DESC, tc.Number, p.OSbitmask, oj.Build DESC, oj.ID DESC

SELECT sq.ID, sq.TestCase, tc.FullName, sq.OS, ISNULL(tr.Build, 0) AS Build, 
       ISNULL(tr.Engineer, 'Nobody') AS Engineer, ISNULL(tr.TestDate, '5/1/2006') AS TestDate, 
	   ISNULL(tr.Result, 'UNTESTED') AS Result, ISNULL(tr.TR, 'N/A') AS TR, pf.OSbitmask, fc.Weight
FROM (SELECT tc.Number AS TestCase, pf.OS, ISNULL(oj.ID, 0) AS ID
      FROM TestCases tc 
      CROSS JOIN Platforms pf 
      LEFT OUTER JOIN (SELECT tr.TestCase, tr.OS, MAX(tr.Build) AS Build, MAX(tr.ID) AS ID
                       FROM TestResults tr
                       WHERE (tr.Project = 'av09120000') AND (tr.Lang = 'USEng') AND (tr.Locale = 'EU')
                       GROUP BY tr.TestCase, tr.OS) oj 
				       ON tc.Number = oj.TestCase AND pf.OS = oj.OS
      WHERE (pf.OSbitmask IN (1, 16, 64))) sq 
      LEFT OUTER JOIN TestResults tr ON tr.ID = sq.ID 
      LEFT OUTER JOIN Platforms pf ON pf.OS = sq.OS 
      LEFT OUTER JOIN TestCases tc ON sq.TestCase = tc.Number 
      LEFT OUTER JOIN FuncComps fc ON LEFT(sq.TestCase, 2) = fc.Number
      LEFT OUTER JOIN Projects pj ON tr.Project = pj.ID
ORDER BY fc.Weight DESC, sq.TestCase, pf.OSbitmask

SELECT sq.ID, sq.TestCase, tc.FullName, sq.OS, ISNULL(tr.Build, 0) AS Build, 
       ISNULL(tr.Engineer, 'Nobody') AS Engineer, ISNULL(tr.TestDate, '5/1/2006') AS TestDate, 
       ISNULL(tr.Result, 'UNTESTED') AS Result, ISNULL(tr.TR, 'N/A') AS TR, pf.OSbitmask, fc.Weight
FROM (SELECT tq.Number AS TestCase, pf.OS, ISNULL(oj.ID, 0) AS ID
      FROM (SELECT t.Number 
	        FROM TestCases t 
            INNER JOIN FuncComps fc ON fc.Number = LEFT(t.Number, 2) 
            INNER JOIN Projects pj ON pj.ID = t.Project AND fc.Weight > 1 
            WHERE (t.Project = 'av09120000')) tq 
      CROSS JOIN Platforms pf 
      LEFT OUTER JOIN (SELECT tr.TestCase, tr.OS, MAX(tr.Build) AS Build, MAX(tr.ID) AS ID
                       FROM TestResults tr 
                       WHERE (tr.Project = 'av09120000') AND (tr.Lang = 'USEng') AND (tr.Locale = 'EU') 
                       GROUP BY tr.TestCase, tr.OS) oj 
      ON tq.Number = oj.TestCase AND pf.OS = oj.OS
      WHERE (pf.OSbitmask IN (1, 16, 64))) sq 
LEFT OUTER JOIN TestResults tr ON tr.ID = sq.ID 
LEFT OUTER JOIN Platforms pf ON pf.OS = sq.OS 
LEFT OUTER JOIN TestCases tc ON sq.TestCase = tc.Number 
LEFT OUTER JOIN FuncComps fc ON LEFT(sq.TestCase, 2) = fc.Number 
LEFT OUTER JOIN Projects pj ON tr.Project = pj.ID
ORDER BY fc.Weight DESC, sq.TestCase, pf.OSbitmask

SELECT tr.TestCase, tr.LogOS as OS, MAX(tr.Build) AS Build, MAX(tr.ID) AS ID
                       FROM TestResults tr 
                       WHERE (tr.Project = 'av09120000') AND (tr.Lang = 'USEng') AND (tr.Locale = 'EU') 
                       GROUP BY tr.TestCase, tr.LogOS