Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Could you please explain the problem(s) you see with #1. –  Leigh Riffel 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? –  Leigh Riffel Feb 17 '12 at 16:46
add comment

1 Answer

up vote 1 down vote accepted

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
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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