Microsoft SQL Server forum - Help with SQL Query

Author

Help with SQL Query

b_naick@yahoo.ca

2005-07-26, 11:24 am

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,tru
e
2,1,User1_Accnt2,tru
e
3,2,User2_Accnt1,fal
se

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?

Razvan Socol

2005-07-26, 11:24 am

> What do I need to change in the query?

Only one word: instead of "WHERE" use "AND".

Razvan

Erland Sommarskog

2005-07-26, 8:24 pm

(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

jsfromynr

2005-07-27, 7:27 am

Hi

May this solve your problem

create Table Users(UserId int primary key,Username varchar(20))

insert into Users(Userid,UserNam
e) values(1,'User1')
insert into Users(Userid,UserNam
e) 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_Ac
cnt1','true')
insert into Accounts VALUES(2,1,'User1_Ac
cnt2','true')
insert into Accounts VALUES(3,2,'User2_Ac
cnt1','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,tru
e
> 2,1,User1_Accnt2,tru
e
> 3,2,User2_Accnt1,fal
se
>
> 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?

Sponsored Links