Problem with Null Value - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Problem with Null Value

Author

Topic

yaman
Posting Yak Master

213 Posts

Posted - 2008-06-04 : 04:14:36

I HAVE A QUERY
"SELECT COUNT(tbl_User.Userid) as Userid ,tbl_Group.GroupName
FROM
dbo.tbl_User
Inner join
dbo.tbl_Group
ON
tbl_User.GroupID=tbl_Group.GroupID Or tbl_user.GroupID=NULL
GROUP BY
tbl_User.groupid ,.tbl_Group.GroupName"

I want Null Data also when I Group Name Column have NULL Value
OUTPUT LIKE THIS , It is Posible Sir

OUTPUT :-
1 Managers
1 QA
1 NULL

Yaman

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-04 : 04:23:51

May be this:-

SELECT COUNT(tmp.Userid),tmp.GroupName
FROM
(SELECT tbl_User.Userid as Userid ,tbl_User.Groupid as Groupid, COALESCE(tbl_Group.GroupName,'NULL') AS GroupName
FROM
dbo.tbl_User
Inner join
dbo.tbl_Group
ON
tbl_User.GroupID=tbl_Group.GroupID Or tbl_user.GroupID IS NULL)tmp
GROUP BY
tmp.groupid, tmp.GroupName

yaman
Posting Yak Master

213 Posts

Posted - 2008-06-04 : 04:39:38

Sir still i Did'nt Got Right Answer
Sir I m Giving to u Data of Table Pls Sir Help me Out :-
Table :- tbl_Group
Groupid Companyid GroupName
4 1 Software
5 1 QA
6 1 Managers
7 3 IT

Table :- tbl_User
USERID GroupId
jkhan NULL
juned 6
vijay 5

SELECT COUNT(tmp.Userid),tmp.GroupName
FROM
(SELECT tbl_User.Userid as Userid ,tbl_User.Groupid as Groupid, COALESCE(tbl_Group.GroupName,'NULL') AS GroupName
FROM
dbo.tbl_User
Inner join
dbo.tbl_Group
ON
tbl_User.GroupID=tbl_Group.GroupID Or tbl_user.GroupID IS NULL)tmp
GROUP BY
tmp.groupid, tmp.GroupName

[/quote]

Yaman

ToniMarieM
Starting Member

6 Posts

Posted - 2008-06-06 : 15:05:35

Use an outer join to get the nulls included. Based on your example, this works.

Toni

declare @group table (groupid int,co_id int, Gname varchar(10)) -- test table 1
-- assume date is next part hh:mm:ssdd

insert into @group
select 4,1,'Software'
union all
select 5,1,'QA'
union all
select 6,1,'Mgrs'
union all
select 7,3,'IT'

select * from @group -- show contents

declare @user table (userid varchar(5),groupid int) -- test table 1
-- assume date is next part hh:mm:ssdd

insert into @user
select 'jkhan',null
union all
select 'juned',6
union all
select 'vijay',5

select * from @user -- show contents

select count(userid), gname
from @user u left outer join @group g on g.groupid = u.groupid
group by gname

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources