Left Join and Where Statements

[rrstudio2]

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

['69 Camaro]

Hi, Andrew.

id Name StatusCode

Never use a Reserved word as a column name. Name and Description are
reserved.

Table: StatusCode

StatusCode Modifier Description

Never use the same names for a table and a column. You'll be sorry when you
start coding.

I don't get this...since it is a left outer join,
why are they being excluded?

It doesn't matter that the query is using a LEFT JOIN to gather the rows.
Your WHERE clause requires that only the rows from the query that have
Modifier = "Job" can be in the resulting data set. You eliminated all other
rows with your qualifier.

Instead, change the names of the table and the columns to avoid introducing
bugs, and use a subquery in the FROM clause (called an inline view) to get
only the rows you want. Try:

SELECT ID, FName, StatusDesc AS Job
FROM Names LEFT JOIN
(SELECT StatusCode, Modifier, StatusDesc
FROM StatusCodes
WHERE Modifier = 'Job') AS SC ON Names.StatusCode = SC.StatusCode;

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact
info.

[DTecMeister]

On Aug 31, 1:34 am, "[e-mail]" <[e-mail]>
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.

['69 Camaro]

Hi.

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.

Unfortunately, that leaves out the rows where Modifier = "Hobby" or any
other string besides the desired "Job." Therefore, Henry, who doesn't also
have a job listed (unlike John, who has both a job and a hobby listed),
won't be included in the resulting data set.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact
info.