Left Join and Where Statements
- From: "rrstudio2@xxxxxxxxxxx" <rrstudio2@xxxxxxxxxxx>
- Date: Thu, 30 Aug 2007 22:34:41 -0700
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
StatusCode.StatusCodeFrom Names LEFT JOIN StatusCode on Names.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
.
- Follow-Ups:
- Re: Left Join and Where Statements
- From: DTecMeister
- Re: Left Join and Where Statements
- From: '69 Camaro
- Re: Left Join and Where Statements
- Prev by Date: Re: Blank Forms
- Next by Date: How to retrieve rows from position n to position m from table in Access 2003
- Previous by thread: Query stops working with ODBC to SQL Server. Help!
- Next by thread: Re: Left Join and Where Statements
- Index(es):
Relevant Pages
|
|
