left outer join - Question

[Darryl]

When you do a sql left outer join in Access 2000, do you have to specify the
joined fields ?

Here is my query:
SELECT newcases.*
FROM newcases LEFT JOIN t_sourcestats ON
[newcases].[sourcecode]=[t_sourcestats].[sourcecode];

Both the newcases table and the t_sourcestats tables have a field
sourcecode. In addition, I have manually viewed the table records and they
do have matching values in those fields.

Problem:
when I run the above query and look at the fields in the returned record,
none of the fields in t_sourcestats appear joined. IE, only fields from
newcases.

what am I missing here ?

thanks,
Darryl

 

[Brendan Reynolds]

You need to specify the fields you want in the SELECT clause ...

SELECT newcases.*, t_sourcestats.* FROM newcases LEFT JOIN t_sourcestats ON
newcases.sourcecode = t_sourcestats.sourcecode

If this doesn't seem intuitive at first, consider that the database engine
can't assume that you want the fields from the joined table implicitly
included in the SELECT clause, as you might have joined the second table
because you wanted to use fields from that table in, for example, a WHERE
clause.

 

[Darryl]

that's the ticket. Worked like a champ.
thanks,
Darryl

You need to specify the fields you want in the SELECT clause ...

SELECT newcases.*, t_sourcestats.* FROM newcases LEFT JOIN t_sourcestats ON
newcases.sourcecode = t_sourcestats.sourcecode

If this doesn't seem intuitive at first, consider that the database engine
can't assume that you want the fields from the joined table implicitly
included in the SELECT clause, as you might have joined the second table
because you wanted to use fields from that table in, for example, a WHERE
clause.

--
Brendan Reynolds

When you do a sql left outer join in Access 2000, do you have to specify
the
joined fields ?

Here is my query:
SELECT newcases.*
FROM newcases LEFT JOIN t_sourcestats ON
[newcases].[sourcecode]=[t_sourcestats].[sourcecode];

Both the newcases table and the t_sourcestats tables have a field
sourcecode. In addition, I have manually viewed the table records and
they
do have matching values in those fields.

Problem:
when I run the above query and look at the fields in the returned record,
none of the fields in t_sourcestats appear joined. IE, only fields from
newcases.

what am I missing here ?

thanks,
Darryl

 

[Marshall Barton]

When you do a sql left outer join in Access 2000, do you have to specify the
joined fields ?

Here is my query:
SELECT newcases.*
FROM newcases LEFT JOIN t_sourcestats ON
[newcases].[sourcecode]=[t_sourcestats].[sourcecode];

Both the newcases table and the t_sourcestats tables have a field
sourcecode. In addition, I have manually viewed the table records and they
do have matching values in those fields.

Problem:
when I run the above query and look at the fields in the returned record,
none of the fields in t_sourcestats appear joined. IE, only fields from
newcases.

what am I missing here ?

Yes, you need to specify the ON clause for either an inner
or an outer join.

The problem is that you never specified the fields you want
from the t_sourcestats table.

SELECT newcases.*, t_sourcestats.*
FROM . . .

However, using * like that will include fields that you do
not want in the result (at least two copies of the
sourcecode field). Regardless of the tediousness of it, you
should specify the (minimum number of) individual fields
that are required in the query's result.