Is a LEFT JOIN with a subquery possible?

[Dave]

I have a list of towns in tblTownData, and a list of people's names in
tblNames. During a year, there are multiple events where more names are
added in tblNames, for a particular town.

ie:
TOWNID EVENTID NAME
1 1 John Doe
1 1 Jane Doe
1 2 Bell E. Dancer
1 2 Frank N. Furter

For each event, I am trying to give feedback to the user as to which towns
have not had a single name added yet. That is, I am trying to generate a
list of towns using a left join on a subquery.

In Access, I can create a query (tblCurrentNamesInTown):
SELECT tblNames.TownID
FROM tblNames
WHERE (((tblNames.ELECTIONID)=1))

then a new query (tblTownData Without Matching Current):
SELECT tblTownData.Municipality
FROM tblTownData LEFT JOIN tblCurrentNamesInTown ON tblTownData.TownID =
tblCurrentNamesInTown.TownID
WHERE (((tblCurrentNamesInTown.TownID) Is Null));

This will return my list fine.

However, I need to call this query result from the database using a SQL
statement inside an ASP page.

Can you create a LEFT JOIN to the subquery (tblCurrentNamesInTown)?
If so, can anyone help with the syntax?
Dave

[jimfortune]

Dave wrote:

I have a list of towns in tblTownData, and a list of people's names in
tblNames. During a year, there are multiple events where more names are
added in tblNames, for a particular town.
...

Did you consider doing something simpler like:

SELECT Municipality FROM tblTownData WHERE TownID NOT IN (SELECT
DISTINCT TownID FROM tblNames WHERE EVENTID = 1);

Of course, if the number of records in tblNames is fairly large the
'NOT IN' syntax becomes inefficient and could explain the route you've
chosen.

James A. Fortune

[billmiami2]

Jim's suggestion is excellent. However, if you must use the join, you
can do the following:

SELECT tblTownData.Municipality
FROM tblTownData LEFT JOIN
(SELECT tblNames.TownID
FROM tblNames
WHERE (((tblNames.ELECTIONID)=1))) AS tblCurrentNamesInTown
ON tblTownData.TownID =
tblCurrentNamesInTown.TownID
WHERE (((tblCurrentNamesInTown.TownID) Is Null));

Bill E.
Hollywood, FL

[Dave]

>I have a list of towns in tblTownData, and a list of people's names in

tblNames. During a year, there are multiple events where more names are
added in tblNames, for a particular town.

James and Bill,

Thanks for the feedback. This seemed to work well. I'm next going to load
the database with roughly the same number of records as I would expect over
several events, and determine which way to go.

Thanks for saving my sanity!

Dave