How to alias a Join?

[colin_e]

(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?

 

[Allen Browne]

Colin, AFAIK, you cannot alias a join, because the join is not named. It is
the table you alias, e.g.:

SELECT A.f1, B.f2 FROM Table1 AS A INNER JOIN Table2 AS B;

You only need to alias the table if you want to use it again in the
subquery. If it is easier, you could alias it in the subquery instead of the
main one.

If the "table" in your query is actually another query, then you can alias
that "table" if you need to.

 

[colin_e]

I have stripped my query down to just this-

SELECT *
FROM Submissions LEFT JOIN Programmes AS NewName
ON Submissions.SubmissionID=Progrmme.SubmissionID

However with the "AS NewName" alias it fails with "syntax error in FROM
clause".

What i'm trying to do here is alias the result of the join (not just alias
the Programmes table). The reason is that I then need to generate one field
as a subquery from the Programmes table, and that will of course include all
the field names from Programmes, so i need to be able to correlate using
table aliases something like the example below, but I clearly haven't got
the syntax right.

SELECT a,b, (SELECT c from Programmes AS P WHERE S.a=P.a)
FROM (Submissions LEFT JOIN Programmes) AS S
ON Submissions.SubmissionID=Programmes.SubmissionID


Any ideas greatly appreciated.

Reagrds: Colin

 

[Tom Ellison]

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

 

[Allen Browne]

Try:
SELECT *
FROM Submissions LEFT JOIN Programmes AS NewName
ON Submissions.SubmissionID = NewName.SubmissionID;

 

[colin_e]

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

 

[Allen Browne]

Colin, take a look at:
Getting a related field from a GroupBy (total) query
at:
http://www.mvps.org/access/queries/qry0020.htm

There are 4 solutions offered.