By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,392 Members | 1,516 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,392 IT Pros & Developers. It's quick & easy.

Access 2003: Left Join won't work - need help please

P: n/a
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

Dec 18 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
"David F" <Sa***********@yahoo.comwrote in
news:11**********************@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

Dec 19 '06 #2

P: n/a

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

Dec 19 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.