Robin-
The instant you apply a predicate to the "right" side of a "left" join, you
nullify the effects of the outer join. You must filter first, then apply the
join, like this:
SELECT ClubsActive.ClubName, tClubPostHolders.YearID, TCPH85.ClubID
FROM ClubsActive LEFT JOIN
(SELECT tClubPostHolders.ClubID, tClubPostHolders.YearID
FROM tClubPostHolders
WHERE (tClubPostHolders.YearID=85) AND (tClubPostHolders.ClubID=19)) As TCPH85
ON ClubsActive.ClubID =
TCPH85.ClubPostID;
John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Robin Chapple
Sent: Saturday, July 16, 2011 4:05 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Join properties in a query
I have failed to understand the 'properties' in the join in a query.
I have one table which I need to show all club names in the resultant
query. The second table will supply the posts which have been filled
and leave me with a result showing the clubs which have failed to
make nominations.
The properties show as: "Include all records from "ClubsActive".
Why do I not se them in the result?
SELECT ClubsActive.ClubName, tClubPostHolders.YearID, tClubPostHolders.ClubID
FROM ClubsActive LEFT JOIN tClubPostHolders ON ClubsActive.ClubID =
tClubPostHolders.ClubPostID
WHERE (((tClubPostHolders.YearID)=85) AND ((tClubPostHolders.ClubID)=19));
Many thanks,
Robin Chapple
------------------------------------
Yahoo! Groups Links
Tidak ada komentar:
Posting Komentar