Combining two queries into a single query

[aine_canby]

Hi there,

I have the following two queries -

Q1:

SELECT TestVersion.TestId, TestVersion.Version FROM (TestVersion LEFT
JOIN Release ON TestVersion.ReleaseId = Release.ReleaseId) LEFT JOIN
ReleasePlan ON Release.ReleaseId = ReleasePlan.ReleaseId WHERE
(((ReleasePlan.StartDate)<=CDate("2006-04-19"))) ORDER BY
TestVersion.TestId;

Q2:

SELECT Max(Q1.Version) AS MaxOfVersion, Q1.TestId FROM Q1 GROUP BY
Q1.TestId;

How do I combine them into just one query?

Thanks in advance,

Aine.

 

[John Spencer]

Perhaps as the following - using a correlated subquery.

SELECT TestVersion.TestId
, TestVersion.Version
, (SELECT Max(Version)
FROM TestVersion as T1
WHERE T1.TestID = TestVersion.TestID) as MaxVersion
FROM (TestVersion LEFT JOIN Release
ON TestVersion.ReleaseId = Release.ReleaseId)
LEFT JOIN ReleasePlan ON Release.ReleaseId = ReleasePlan.ReleaseId
WHERE (((ReleasePlan.StartDate)<=CDate("2006-04-19")))
ORDER BY TestVersion.TestId;

By the way applying criteria to ReleasePlan.StartDate is could negate your
Left Join's. You ***M I G H T*** be able to fix that by using
WHERE ReleasePlan.StartDate<=CDate("2006-04-19") OR ReleasePlan.StartDate
is Null

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[aine_canby]

Perhaps as the following - using a correlated subquery.

SELECT TestVersion.TestId
, TestVersion.Version
, (SELECT Max(Version)
FROM TestVersion as T1
WHERE T1.TestID = TestVersion.TestID) as MaxVersion
FROM (TestVersion LEFT JOIN Release
ON TestVersion.ReleaseId = Release.ReleaseId)
LEFT JOIN ReleasePlan ON Release.ReleaseId = ReleasePlan.ReleaseId
WHERE (((ReleasePlan.StartDate)<=CDate("2006-04-19")))
ORDER BY TestVersion.TestId;

By the way applying criteria to ReleasePlan.StartDate is could negate your
Left Join's. You ***M I G H T*** be able to fix that by using
WHERE ReleasePlan.StartDate<=CDate("2006-04-19") OR ReleasePlan.StartDate
is Null

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.










- Visa citerad text -

Thanks for the reply.

Your solution though is not giving me what I want, but I haven't
explained myself at all well.

I actually require 3 queries to get what I need, but I'd like to use
just one.


Q1:

SELECT TestVersion.TestCaseId, TestVersion.Version
FROM ((TestVersion INNER JOIN Release ON
TestVersion.ReleaseId=Release.ReleaseId) INNER JOIN TestCase ON
TestVersion.TestCaseId=TestCase.TestCaseID) INNER JOIN ReleasePlan ON
Release.ReleaseId=ReleasePlan.ReleaseId
WHERE (((TestCase.TestCase)="Test21.B") AND
((ReleasePlan.StartDate)<=CDate("2004-01-19")))
ORDER BY TestVersion.TestCaseId;

Q2:

SELECT Max(Q1.Version) AS MaxOfVersion, Q1.TestCaseId
FROM Q1
GROUP BY Q1.TestCaseId;

Q3:

SELECT TestVersion.VersionId
FROM Q2 INNER JOIN TestVersion ON (Q2.TestCaseId =
TestVersion.TestCaseId) AND (Q2.MaxOfVersion = TestVersion.Version);

I have 4 tables. TestCase stores information about tests I perform in
the lab. Certain tests have more than one version, and the TestVersion
table therefore contains a TestCaseId field concerned with primary
keys in TestCase, as well as ReleaseId which stores primary keys
concerned with the Release table. The Release table in turn holds
primary keys to the ReleasePlan table which has a field called
StartDate.

Therefore, date within the database takes the following form

TestCase.TestCase / TestVersion.Version / ReleasePlan.StartDate
Test21.A / 1 / "2002-02-22"
Test21.A / 2 / "2003-01-19"
Test21.A / 3 / "2005-03-11"
Test21.C / 1 / "2006-04-03"

So this is what I wan't to do. I want to find the
TestVersion.TestVersionId concerned with a certain test case. In Q1
above, the test case is "Test21.B". But I must also take into account
that multiple version of a test case exist. So I specify a date also
in Q1 ("2004-01-19") such that this query gives me all Test Cases
who's versions have a ReleasePlan.StartDate equal to or prior to this
date. Therefore Q1 gives me information concerned with -

Test21.A / 1 / "2002-02-22"
Test21.A / 2 / "2003-01-19"

I then use Q2 above to get the maximum test version prior to this
date. Which gives me information concerned with -

Test21.A / 2 / "2003-01-19"

Finally, Q3 is used to extract TestVersion.VersionId and this is
exactly what I'm looking for.

I hope that makes sense.

So how do I do these 3 queries in just one?

Thanks,

Aine