- 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;
My data is as follows:
Users
=====
1, User1
2, User2
Accounts
========
1,1,User1_Accnt1,true
2,1,User1_Accnt2,true
3,2,User2_Accnt1,false
The expected output is :
1, User1, 2
2, User2, 0
But I get,
1, User1, 2
What do I need to change in the query?> What do I need to change in the query?
Only one word: instead of "WHERE" use "AND".
Razvan|||(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/techin.../2000/books.asp|||Hi
May this solve your problem
create Table Users(UserId int primary key,Username varchar(20))
insert into Users(Userid,UserName) values(1,'User1')
insert into Users(Userid,UserName) values(2,'User2')
Create Table Accounts(AccountNo int primary key,UserId int references
Users,AccountName varchar(20),Active varchar(20))
insert into Accounts VALUES(1,1,'User1_Accnt1','true')
insert into Accounts VALUES(2,1,'User1_Accnt2','true')
insert into Accounts VALUES(3,2,'User2_Accnt1','false')
select U.*,count(case when A.Active='true' then 1 else null end) from
Users U, Accounts A
where U.userid = A.userid
group by U.Userid,U.UserName
Drop Table Accounts
Drop Table Users
Please do post DDL , DML as it become easy for others to test their
queries
With warm regards
Jatinder Singh
b_naick@.yahoo.ca wrote:
> 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;
>
> My data is as follows:
> Users
> =====
> 1, User1
> 2, User2
> Accounts
> ========
> 1,1,User1_Accnt1,true
> 2,1,User1_Accnt2,true
> 3,2,User2_Accnt1,false
> The expected output is :
> 1, User1, 2
> 2, User2, 0
> But I get,
> 1, User1, 2
> What do I need to change in the query?
