Re: Left Join and Where Statements

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/expert_contributors2.html for contact
info.


.