Posted by Erland Sommarskog on 10/01/98 11:22

(b_naick@yahoo.ca) writes:
> I have 2 tables:
>
> - Users, with fields id and fullName
> - Accounts, with accntID, userID, accntName, Active
>
> I need to write a query which lists all the users, along with the
> number of accounts they have.
>
> I am using the following query -
>
> SELECT U.id, U.fullName, Count(A.accntID) AS CountOfaccntID
> FROM users AS U LEFT JOIN accounts AS A ON U.id = A.userID
> WHERE A.active=Yes
> GROUP BY U.id, U.fullName;

To explain Razvan's answer a little more, this is what is happening:

Logically, in an SQL query, you start with the table in the FROM
clause, and then you build a new table every time you tack on a
new table with a JOIN operator (this can be changed with parentheses).
Eventually, the resulting table is filtered by the WHERE clause.

So you start with Users, and left-join it to Accounts. You now have
a table which has all the original rows in Users. For the matching
columns in Accounts, the columns from Accounts have the value from
that table. From the non-matching rows, you have NULL. Then comes the
WHERE clause, which says "A.Active=Yes". Which means that all rows
with NULL in A.Active are filtered away. That is, all those rows
from Users with no matching accounts are no longer in the result set.

When you change WHERE to AND, the condition A.Active=Yes moves to
the JOIN operation. This means that only the rows from Accounts
with Active=Yes are brought in, and remaining rows have NULL in
all columns. In your original query, the rows with Active=No had
values in Accounts in that intermediate table (which is only locigal).

This is indeed a common error to make, and it took me sometime as well
to understand how the FROM-JOIN co-operates with WHERE, when I started
to use this syntax.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]