SQL Statement

[Crazyhorse]

I've got two tables. I want to perform a LEFT JOIN between the first table
and a subset of the second table (so that each member of table1 has no more
than one match in the subset of table2). If SQL understood my randomly
generated syntax, it would run the following:

SELECT Table1.*, Table2.*
FROM Table1
LEFT JOIN (SELECT * FROM Table2 Where Somefield=1)
ON Table1.PrimaryKey=Table2.ForeignKey

But it doesn't. So how do I perform Joins on subqueries in SQL??

 

[Conan Kelly]

Crazyhorse,

In all honesty, how I would do this is:
--set up my subquery (Table2) in Query Design view
--switch to SQL view
--surround the subquery SQL statement with "SELECT * FROM ({sub query sql
statement here}) as AliasName"
--switch to design view
--add Table1
--create joins
--add fields, criteria, etc.

Then you could switch back to SQL view and see what Access came up with so
you would know what to do in the future.

HTH,

Conan

 

[John Spencer]

You need to assign an alias to the subquery so you can reference it in
the on clause and elsewhere in your query.

Also, be aware that for this to work Access requires that the field and
table names of the subquery (used in a from clause) must not require
brackets. Therefore the names should consist of letters, numbers, and
underscores - no spaces or other "special" characters allowed.

SELECT Table1.*, X.*
FROM Table1
LEFT JOIN
(SELECT *
FROM Table2
Where Somefield=1) as X
ON Table1.PrimaryKey=X.ForeignKey


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