469,282 Members | 1,924 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,282 developers. It's quick & easy.

Is a LEFT JOIN with a subquery possible?

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
Nov 13 '05 #1
3 21631

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

Nov 13 '05 #2
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

Nov 13 '05 #3
>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
Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by stabbert | last post: by
4 posts views Thread by polycom | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.