Re: trouble converting SQL subquery to stacked saved queries

Tech-Archive recommends: Fix windows errors by optimizing your registry

OK - thanks for the explanation, Sylvain.

Becky

"Sylvain Lafontaine" wrote:

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)


"Becky" <Becky@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:FE3A0530-FF47-4410-83C2-ECF0BCD10D2C@xxxxxxxxxxxxxxxx
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


"Sylvain Lafontaine" wrote:

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)


"Becky" <Becky@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:86B89198-8112-46E7-8E01-3BA59C90BE47@xxxxxxxxxxxxxxxx
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







.

Relevant Pages

  • RE: XML Cross Apply ...
    ... "The APPLY operator allows you to invoke a table-valued function for each ... row returned by an outer table expression of a query. ... CROSS APPLY allows us to use the nodes method (or ... I believe that the discrepancy between your query return and mine is due to ...
    (microsoft.public.sqlserver.xml)
  • Re: trouble converting SQL subquery to stacked saved queries
    ... queries can sometimes be faster than subqueries. ... This was saved as a query called qryQ2. ... I don't see how using this query would save you some work for the outer ...
    (microsoft.public.access.queries)
  • Re: Query with left outer join all of a sudden wont work?
    ... I did change the join between the queries to an inner join and it worked. ... just doesn't work if it is a left or right outer join. ... rather than on the top most query calling it). ... can you post the whole SQL statement? ...
    (microsoft.public.access.queries)
  • RE: XML Cross Apply ...
    ... So in your query, wouldn't you need to use OUTER APPLY instead of CROSS ... In the following code 'noder' is a table that contains an xml column named ...
    (microsoft.public.sqlserver.xml)
  • Re: trouble converting SQL subquery to stacked saved queries
    ... This is only true - and not always but sometimes - when the subquery is ... Also, in your case, the qryQ2 query is not a valid one. ... direct dependancy from the outer query. ...
    (microsoft.public.access.queries)