Re: Left Join and Where Statements

On Aug 31, 1:34 am, "rrstud...@xxxxxxxxxxx" <rrstud...@xxxxxxxxxxx>
wrote:
If I have two tables and need to do a left outer join and include a
where statement on the second table, it seems like the left outer join
becomes an inner join. For example:

Table: Names

id Name StatusCode
1 John 11
2 Henry 22
3 Bob 33
4 Sue 44

Table: StatusCode

StatusCode Modifier Description
11 Job Tech
11 Hobby Piano
22 Hobby Video
33 Job IRS

In Access, I need to combine the two tables to get
id Name Job
1 John Tech
2 Henry
3 Bob IRS
4 Sue

I assumed you could do this with a statement like
Select Names.*, StatusCode.Modifier, StatusCode.Description>From Names LEFT JOIN StatusCode on Names.StatusCode =

StatusCode.StatusCode
Where StatusCode.Modifier = "Job"
(I know the code syntax isn't quite correct, but access creates the
correct syntax and the question is more related to function).

Using this, the query seems to exclude Henry and Sue because they
don't have a Job. I don't get this...since it is a left outer join,
why are they being excluded? If I remove the where statement, it
displays all the names but the people like John are listed on two
rows, one with the Job and one with the hobby. Any ideas on how to do
this? Do I need to create a sub-query to first pull just the items
from the StatusCode table with a StatusModifier of Job? It seems like
this works, but I don't get why the left join isn't working.

Thanks,
Andrew V. Romero

The problem is in the where statement.
Try: Where StatusCode.Modifier = "Job" OR StatusCode.Modifier is null
All columns in the right side of the join will be null for rows that
don't have matching entries.

.

Relevant Pages

  • Left Join and Where Statements
    ... where statement on the second table, it seems like the left outer join ... Henry 22 ... Table: StatusCode ... John Tech ...
    (comp.databases.ms-access)
  • Re: Non MRs and Blanks to show
    ... Use a criterion on statuscode of ... John W. Vinson [MVP] ...
    (microsoft.public.access.queries)