Problem Getting Left Part of Join to return rows

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Problem Getting Left Part of Join to return rows

Author

Topic

paultervit
Starting Member

10 Posts

Posted - 2009-01-14 : 11:39:50

Hello
I am having a problem with a sql query I am trying to write. I have two tables e.g.

Table 1

User
1
2
3
4

Table 2
Login Name
12:30 1
12:45 2
12:46 1
13:15 3

I would like to return the following

Name Login Count
1 2
2 1
3 1
4 0

My query so far uses a join and a count but only returns the rows with entrys in both tables. Is there an easy way to get the return I am looking for?

P.s. my current query is as follows

SELECT 1.User, COUNT(2.Login) Cnt
FROM table1 1 join table2 2 on 1.name = 2.name
GROUP BY 1.name
ORDER BY Cnt DESC
Thanks

Paul

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-14 : 11:52:41

use left join


SELECT 1.User, COUNT(2.Login) Cnt 
FROM table1 1 left join table2 2 on 1.name = 2.name
GROUP BY 1.name
ORDER BY Cnt DESC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources