Hello,

I have what seems like a simple left join query,
but Access returns results like it's an inner join. I've tried
numerous combinations without success.

I have a table (ProjectList) with one row per project.
Each project has a status condition of Green, Yellow
or Red. The other table (DisplayOrder) has three rows,
one for each status condition color.

My query needs to return three rows: Green, Yellow and
Red plus the number of projects for each color condition.
One row for each color, even if no projects have
that color status.

Each time I run the query, it only returns *two* rows,
Green and Yellow, but never Red (currently, there are no
Red projects).

Why does the left join not show a red row from the
DisplayOrder table?

Thanks in advance for any and all suggestions,
David

Here's the query:

SELECT DisplayOrder.Condition,
Count(*) AS Total_Projects
FROM DisplayOrder
LEFT JOIN ProjectList
ON DisplayOrder.Condition = ProjectList.Condition
WHERE ProjectList.Status = 'Active'
AND ProjectList.Program = 'AML'
GROUP BY DisplayOrder.Condition, DisplayOrder.DisplayOrder
ORDER BY DisplayOrder.DisplayOrder

"David F" <Save_redwoods@yahoo.comwrote in
news:1166469699.056824.289910@n67g2000cwd.googlegr oups.com:

Hello,
>
I have what seems like a simple left join query,
but Access returns results like it's an inner join. I've

tried

numerous combinations without success.
>
I have a table (ProjectList) with one row per project.
Each project has a status condition of Green, Yellow
or Red. The other table (DisplayOrder) has three rows,
one for each status condition color.
>
My query needs to return three rows: Green, Yellow and
Red plus the number of projects for each color condition.
One row for each color, even if no projects have
that color status.
>
Each time I run the query, it only returns *two* rows,
Green and Yellow, but never Red (currently, there are no
Red projects).
>
Why does the left join not show a red row from the
DisplayOrder table?
>
Thanks in advance for any and all suggestions,
David
>
Here's the query:
>
SELECT DisplayOrder.Condition,
Count(*) AS Total_Projects
FROM DisplayOrder
LEFT JOIN ProjectList
ON DisplayOrder.Condition = ProjectList.Condition
WHERE ProjectList.Status = 'Active'
AND ProjectList.Program = 'AML'
GROUP BY DisplayOrder.Condition, DisplayOrder.DisplayOrder
ORDER BY DisplayOrder.DisplayOrder
>

Queries apply the where clause after building the resultset from
the joins.

Solutions:
1) move the WHERE to a different query that just filters the
projectlist and left join that query with Displayorder.
2) Change the where clause so that you get null responses.
WHERE (ProjectList.Status = 'Active'
OR ProjectList.Status is Null)
AND (ProjectList.Program = 'AML'
OR ProjectList.Program is Null)

Note the use of parentheses () to get the right nesting of
conditions.

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Bob Quintal wrote:

Queries apply the where clause after building the resultset from
the joins.
>
Solutions:
1) move the WHERE to a different query that just filters the
projectlist and left join that query with Displayorder.
2) Change the where clause so that you get null responses.
WHERE (ProjectList.Status = 'Active'
OR ProjectList.Status is Null)
AND (ProjectList.Program = 'AML'
OR ProjectList.Program is Null)
>
Note the use of parentheses () to get the right nesting of
conditions.
>

Bob,

That makes sense now; I wasn't aware about the order of the WHERE
processing taking place after the join. I used your option #1 and
query now works. Thanks!!

David