trouble converting SQL subquery to stacked saved queries

[Becky]

hello to all

I'd like to run the following SQL as stacked queries, so that I can compare
the executiom times for each. The 'inner' SELECT find the top 3 scoring
employees from each Org. The 'outer' SELECT uses these results to pick the
Org with the highest total score.

SELECT TOP 1 Q1.OrgID, Q1.OrgName, Sum(Q1.Score) AS Total
FROM qryEmp AS Q1
WHERE (((Q1.EmpID) In ((
SELECT Top 3 Q2.EmpID
FROM [qryEmp] As Q2
WHERE (Q2.OrgID = Q1.OrgID)
ORDER BY Score DESC, EmpID
))
))
GROUP BY Q1.OrgID, Q1.OrgName
ORDER BY Sum(Q1.Score) DESC , Q1.OrgName;

I've turned the 'inner' SELECT in a saved query (qryQ2), but I'm having
trouble with the 'outer' query. Any clues? I'm guessing I don't know how to
write the IN(???).

much thanks
Becky

 

[Sylvain Lafontaine]

First, you are probably losing your time because even when you are using
stacked queries instead of subqueries, JET will still process it as a single
whole query.

Second, you don't tell us what is the exact SQL text under which you have
saved your qryQR2. However, if you have writen something like:

SELECT Q2.EmpID FROM [qryEmp]

I don't see how using this query would save you some work for the outer
query:

SELECT TOP 1 Q1.OrgID, Q1.OrgName, Sum(Q1.Score) AS Total
FROM qryEmp AS Q1
WHERE (((Q1.EmpID) In ((
SELECT Top 3 Q2.EmpID
FROM [qryQ2] As Q2
WHERE (Q2.OrgID = Q1.OrgID)
ORDER BY Score DESC, EmpID
))
))
GROUP BY Q1.OrgID, Q1.OrgName
ORDER BY Sum(Q1.Score) DESC , Q1.OrgName;

If you have added a parameter to your qryQ2 then in my opinion, you cannot
use it as a subquery. Parameters can only be used to transmit a value from
the user and not from an outer query.

 

[Becky]

hello

I'm just trying to investigate advice I saw from Allen Browne that stacked
queries can sometimes be faster than subqueries. The example I used was just
an illustrative example. My 'inner' query was just..

SELECT Top 3 Q2.EmpID
FROM [qryQ2] As Q2
WHERE (Q2.OrgID = Q1.OrgID)
ORDER BY Score DESC, EmpID

This was saved as a query called qryQ2. What I need assistance on is how to
do the 'outer' query.

thank you
Becky

First, you are probably losing your time because even when you are using
stacked queries instead of subqueries, JET will still process it as a single
whole query.

Second, you don't tell us what is the exact SQL text under which you have
saved your qryQR2. However, if you have writen something like:

SELECT Q2.EmpID FROM [qryEmp]

I don't see how using this query would save you some work for the outer
query:

SELECT TOP 1 Q1.OrgID, Q1.OrgName, Sum(Q1.Score) AS Total
FROM qryEmp AS Q1
WHERE (((Q1.EmpID) In ((
SELECT Top 3 Q2.EmpID
FROM [qryQ2] As Q2
WHERE (Q2.OrgID = Q1.OrgID)
ORDER BY Score DESC, EmpID
))
))
GROUP BY Q1.OrgID, Q1.OrgName
ORDER BY Sum(Q1.Score) DESC , Q1.OrgName;

If you have added a parameter to your qryQ2 then in my opinion, you cannot
use it as a subquery. Parameters can only be used to transmit a value from
the user and not from an outer query.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

hello to all

I'd like to run the following SQL as stacked queries, so that I can
compare
the executiom times for each. The 'inner' SELECT find the top 3 scoring
employees from each Org. The 'outer' SELECT uses these results to pick
the
Org with the highest total score.

SELECT TOP 1 Q1.OrgID, Q1.OrgName, Sum(Q1.Score) AS Total
FROM qryEmp AS Q1
WHERE (((Q1.EmpID) In ((
SELECT Top 3 Q2.EmpID
FROM [qryEmp] As Q2
WHERE (Q2.OrgID = Q1.OrgID)
ORDER BY Score DESC, EmpID
))
))
GROUP BY Q1.OrgID, Q1.OrgName
ORDER BY Sum(Q1.Score) DESC , Q1.OrgName;

I've turned the 'inner' SELECT in a saved query (qryQ2), but I'm having
trouble with the 'outer' query. Any clues? I'm guessing I don't know how
to
write the IN(???).

much thanks
Becky

 

[Sylvain Lafontaine]

This is only true - and not always but sometimes - when the subquery is
independant from the outer query; clearly not the case here because of the
condition « Q2.OrgID = Q1.OrgID »

Also, in your case, the qryQ2 query is not a valid one. You can use stacked
queries only when the subquery can be seen as an ordinary View, with no
direct dependancy from the outer query.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

hello

I'm just trying to investigate advice I saw from Allen Browne that stacked
queries can sometimes be faster than subqueries. The example I used was
just
an illustrative example. My 'inner' query was just..

SELECT Top 3 Q2.EmpID
FROM [qryQ2] As Q2
WHERE (Q2.OrgID = Q1.OrgID)
ORDER BY Score DESC, EmpID

This was saved as a query called qryQ2. What I need assistance on is how
to
do the 'outer' query.

thank you
Becky

First, you are probably losing your time because even when you are using
stacked queries instead of subqueries, JET will still process it as a
single
whole query.

Second, you don't tell us what is the exact SQL text under which you have
saved your qryQR2. However, if you have writen something like:

SELECT Q2.EmpID FROM [qryEmp]

I don't see how using this query would save you some work for the outer
query:

SELECT TOP 1 Q1.OrgID, Q1.OrgName, Sum(Q1.Score) AS Total
FROM qryEmp AS Q1
WHERE (((Q1.EmpID) In ((
SELECT Top 3 Q2.EmpID
FROM [qryQ2] As Q2
WHERE (Q2.OrgID = Q1.OrgID)
ORDER BY Score DESC, EmpID
))
))
GROUP BY Q1.OrgID, Q1.OrgName
ORDER BY Sum(Q1.Score) DESC , Q1.OrgName;

If you have added a parameter to your qryQ2 then in my opinion, you
cannot
use it as a subquery. Parameters can only be used to transmit a value
from
the user and not from an outer query.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

hello to all

I'd like to run the following SQL as stacked queries, so that I can
compare
the executiom times for each. The 'inner' SELECT find the top 3
scoring
employees from each Org. The 'outer' SELECT uses these results to pick
the
Org with the highest total score.

SELECT TOP 1 Q1.OrgID, Q1.OrgName, Sum(Q1.Score) AS Total
FROM qryEmp AS Q1
WHERE (((Q1.EmpID) In ((
SELECT Top 3 Q2.EmpID
FROM [qryEmp] As Q2
WHERE (Q2.OrgID = Q1.OrgID)
ORDER BY Score DESC, EmpID
))
))
GROUP BY Q1.OrgID, Q1.OrgName
ORDER BY Sum(Q1.Score) DESC , Q1.OrgName;

I've turned the 'inner' SELECT in a saved query (qryQ2), but I'm having
trouble with the 'outer' query. Any clues? I'm guessing I don't know
how
to
write the IN(???).

much thanks
Becky

 

[Becky]

OK - thanks for the explanation, Sylvain.

Becky

This is only true - and not always but sometimes - when the subquery is
independant from the outer query; clearly not the case here because of the
condition « Q2.OrgID = Q1.OrgID »

Also, in your case, the qryQ2 query is not a valid one. You can use stacked
queries only when the subquery can be seen as an ordinary View, with no
direct dependancy from the outer query.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

hello

I'm just trying to investigate advice I saw from Allen Browne that stacked
queries can sometimes be faster than subqueries. The example I used was
just
an illustrative example. My 'inner' query was just..

SELECT Top 3 Q2.EmpID
FROM [qryQ2] As Q2
WHERE (Q2.OrgID = Q1.OrgID)
ORDER BY Score DESC, EmpID

This was saved as a query called qryQ2. What I need assistance on is how
to
do the 'outer' query.

thank you
Becky

First, you are probably losing your time because even when you are using
stacked queries instead of subqueries, JET will still process it as a
single
whole query.

Second, you don't tell us what is the exact SQL text under which you have
saved your qryQR2. However, if you have writen something like:

SELECT Q2.EmpID FROM [qryEmp]

I don't see how using this query would save you some work for the outer
query:

SELECT TOP 1 Q1.OrgID, Q1.OrgName, Sum(Q1.Score) AS Total
FROM qryEmp AS Q1
WHERE (((Q1.EmpID) In ((
SELECT Top 3 Q2.EmpID
FROM [qryQ2] As Q2
WHERE (Q2.OrgID = Q1.OrgID)
ORDER BY Score DESC, EmpID
))
))
GROUP BY Q1.OrgID, Q1.OrgName
ORDER BY Sum(Q1.Score) DESC , Q1.OrgName;

If you have added a parameter to your qryQ2 then in my opinion, you
cannot
use it as a subquery. Parameters can only be used to transmit a value
from
the user and not from an outer query.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


hello to all

I'd like to run the following SQL as stacked queries, so that I can
compare
the executiom times for each. The 'inner' SELECT find the top 3
scoring
employees from each Org. The 'outer' SELECT uses these results to pick
the
Org with the highest total score.

SELECT TOP 1 Q1.OrgID, Q1.OrgName, Sum(Q1.Score) AS Total
FROM qryEmp AS Q1
WHERE (((Q1.EmpID) In ((
SELECT Top 3 Q2.EmpID
FROM [qryEmp] As Q2
WHERE (Q2.OrgID = Q1.OrgID)
ORDER BY Score DESC, EmpID
))
))
GROUP BY Q1.OrgID, Q1.OrgName
ORDER BY Sum(Q1.Score) DESC , Q1.OrgName;

I've turned the 'inner' SELECT in a saved query (qryQ2), but I'm having
trouble with the 'outer' query. Any clues? I'm guessing I don't know
how
to
write the IN(???).

much thanks
Becky