ACCESS FEED: RE: [MS_AccessPros] Join properties in a query

Sabtu, 16 Juli 2011

RE: [MS_AccessPros] Join properties in a query

Robin-

Ooops. I left out the Join field in the inner SELECT. Try this:

SELECT ClubsActive.ClubName, tClubPostHolders.YearID, TCPH85.ClubID
FROM ClubsActive LEFT JOIN
(SELECT tClubPostHolders.ClubID, tClubPostHolders.YearID,
tClubPostHolders.ClubPostID
FROM tClubPostHolders
WHERE (tClubPostHolders.YearID=85) AND (tClubPostHolders.ClubID=19)) As TCPH85
ON ClubsActive.ClubID =
TCPH85.ClubPostID;

You could have also created a separate query that looks like:

SELECT tClubPostHolders.ClubID, tClubPostHolders.YearID,
tClubPostHolders.ClubPostID
FROM tClubPostHolders
WHERE (tClubPostHolders.YearID=85) AND (tClubPostHolders.ClubID=19)

.. and then use that in your LEFT JOIN with ClubsActive. All I've done is embed
that query in the FROM clause of your original query, and I gave it the name
"TCPH85". By embedding the query, I'm applying the filter for YearID and
ClubID, THEN doing the outer join with ClubsActive. That should get you ALL
rows in ClubsActive and any matching rows from tClubPostHolders where the year
is 85 and the club is 19.

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/> http://www.viescas.com/

(Paris, France)

From: Robin Chapple [mailto:robinski@mymail.net.au]
Sent: Saturday, July 16, 2011 9:08 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] Join properties in a query

Thanks John,

I've not understood the SQL. Perhaps an explanation is steps might help.

I've not met the term 'filter' applied to a query! When you advise to apply the
filter first am I right to assume that happens in the one SQL statement?

It gives me an error which is here:

http://www.rotary9790.org.au/test/test.asp

Robin

At 16/07/2011 03:12 PM, you wrote:

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

------------------------------------

Yahoo! Groups Links

[Non-text portions of this message have been removed]