Re: How to alias a Join->Correlated Subquery
- From: "colin_e" <coline@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Fri, 10 Feb 2006 03:19:27 -0800
Hi Guys, thanks for your replies. I have been away for a couple of days,
hence why i have not come back to you.
I now understand that Allen Browne was correct. You can't alias a join
(unless you save it as a query in it's own right), you can only alias the
tables in the join.
Having got over that point of learning, can I lay the full problem out for
you.
Objects
--------
Org- I have a table of Organisations, storing a single
hierarchy of Orgs.
Submission- Organisations should (but may not) submit a plan.
Programme- Each submission (plan) can contain 0 or more programmes with
names and expected dates for their programme plans to be
submitted.
Query
------
I want a query to list:
1) The organisations.
2) The submission dates.
3) The date and name of the first programme submission (by date) planned by
that org.
It's (3) that's causing me problems.
Note: Because not all Orgs may have a submission I have had to create a
separate query to get around Access' limitations with outer joins. The query
"_OJ_Org_Submission" lists all Orgs and their Submissions.
Attempt 1
----------
SELECT OS.*
, (SELECT * FROM Programme AS P
WHERE P.SubmissionID = OS.SubmissionID
AND P.DateSchedSubmit = Min(P.DateSchedSubmit))
FROM _OJ_Org_Submission AS OS
This fails with the error "Cannot have an aggregate function in a WHERE
clause".
This surprises me because i'm pretty sure this syntax works in multiple
other DBMS'.
Attempt 2
----------
SELECT OS.*
, (SELECT TOP 1 * FROM Programme AS P
WHERE P.SubmissionID = OS.SubmissionID
ORDER BY DateSchedSubmit)
FROM _OJ_Org_Submission AS OS
Fails with the error "You have written a subquery that can return more than
one field..."
Only one field per subquery? This could get painful. Does it take 10
subqueries (with identical WHERE clauses) to return 10 fields?
Attempt 3 (just the Programme Name)
----------
SELECT OS.*
, (SELECT TOP 1 ProgName FROM Programme AS P
WHERE P.SubmissionID = OS.SubmissionID
ORDER BY DateSchedSubmit)
FROM _OJ_Org_Submission AS OS
Fails with the error "At most one record can be returned by this subquery".
1) Isn't that exactly what the "TOP 1" clause means?
2) I thought the "TOP 1" and "Order By" combination was the standard way in
Access of constructing the equivalent of a "Where Date=Max(Date)" type
Select query.
Any ideas on how to approach this?
Regards: Colin
"Tom Ellison" wrote:
Dear Colin:.
You should be able to alias the JOINed tables if you make a complete query
out of it, and alias that:
SELECT J1.f1, J1.f2
FROM (SELECT * FROM t1 INNER JOIN t2) AS J1
It is surprising not to have an ON clause for the JOIN. Perhaps this is a
simplification of what you actually have.
Tom Ellison
"colin_e" <coline@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:C1C7BE8B-3743-49E1-84AD-53CE8A83DEE2@xxxxxxxxxxxxxxxx
(MDB)
I am trying to create a correlated subquery. The docs recommend aliasing
the
main query so the subquery can refer to equivalent fields. However the
docs
show only a simple example based on one table.
I am falling over on the step of creating an alias for the result of a
join.
I've tried:
SELECT J1.f1, J1.f2 FROM (t1 INNER JOIN t2) AS J1
but this just gets me a "syntax error in From clause", with or without
braces. What am I missing here?
- Follow-Ups:
- Re: How to alias a Join->Correlated Subquery
- From: Allen Browne
- Re: How to alias a Join->Correlated Subquery
- References:
- Re: How to alias a Join?
- From: Tom Ellison
- Re: How to alias a Join?
- Prev by Date: Re: Query Results
- Next by Date: Re: How do I do bitwise compare operations on a field?
- Previous by thread: Re: How to alias a Join?
- Next by thread: Re: How to alias a Join->Correlated Subquery
- Index(es):
Relevant Pages
|
