1

I want to redesign a part of my system. I got the following objects:

Site:
id  
Name

Site Version:
id
idSite
Name

Task:
id
idSite
Name

Test:
id
idTask
Name

TestResult:
idTest
idSiteVersion
dateStarted
dateEnded
result

Two problems:

  1. You can see that I have a circle in the definitions. The problem is, I got Tasks defined for Sites, and also through the TestResult > SiteVersion > Site I can get back to Site again.

  2. The main query to answer is, for a given Site, show all SiteVersion and their compatibility to Tasks defined for them.

3
  • Could you please explain the problem(s) you see with #1. Feb 16 '12 at 16:05
  • i thought it was wrong at the time, but I understood later that it's ok @LeighRiffel
    – shevski
    Feb 17 '12 at 16:42
  • So basically there is no longer a question here. If so, could you please close it? Feb 17 '12 at 16:46
1

I've solved 2. using sub-querys

SELECT
  tr1.id AS id,
  dsv.idSite_id AS idSite,
  tr1.idSiteVersion_id AS idSiteVersion,
  tr1.idTest_id AS idTest,
  tr1.result AS result,
  tr1.timeStarted AS timeStarted,
FROM 
  dash.testresult AS tr1 
  NATURAL JOIN (
    SELECT idTest_id, idSiteVersion_id, MAX(timeEnd) AS timeEnd
    FROM dash.testresult
    WHERE timeEnd IS NOT NULL
    GROUP BY idTest_id, idSiteVersion_id
  ) AS tr2
  JOIN dash.siteversion AS dsv
    ON dsv.id = tr1.idSiteVersion_id
  JOIN dash.test AS dtest
    ON dtest.id = tr1.idTest_id AND dtest.ignored = 0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.