Query Problem - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Query Problem

Author

Topic

swathigardas
Posting Yak Master

149 Posts

Posted - 2009-03-18 : 06:59:47

declare @supportcenter table (support_id int , name varchar(50))
declare @supportmanager table (support_id int , manager varchar(50))
declare @group table (group_id int, group_name varchar(50))
declare @support_group table(support_id int, group_id int, group_supervisor varchar(50))
declare @group_members table (group_id int, member_nm varchar(50))
declare @application table (app_id varchar(5), appl_name varchar(20))
declare @Group_application table (group_id int , app_id varchar(5))

insert into @supportcenter (support_id,name) select 1,'abc'
union all Select 2,'def'

insert into @supportmanager (support_id,manager) select 1,'madhu'
union all select 2,'aravind'

insert into @group (group_id,group_name) select 1,'group1'
union all select 2,'group2'
union all select 3,'group3'

insert into @support_group (support_id ,group_id,group_supervisor) select 1,1,'sadhu'
union all select 1,2,'mohan'
union all select 2,3,'pallavi'

insert into @group_members (group_id,member_nm) select 1,'sadhu'
union all select 2,'mohan'
union all select 3,'pallavi'
UNION ALL select 1,'syam'
UNION ALL select 1,'prem'
UNION ALL select 2,'meena'
UNION ALL select 2,'brama'
UNION ALL select 3,'mike'

insert into @Application (app_id,appl_name) Select 'app1','Application 1 '
union all select 'app2','application2'
union all select 'app3','application3'

insert into @group_application (group_id , app_id )select 1,'appl'
union all select 2,'app2'
union all select 2, 'app3'
declare @user_nm varchar(50)
set @user_nm='aravind'

select distinct sc.support_id,scg.group_supervisor, scm.manager,a.appl_name
from @supportcenter sc
INNER join @supportmanager scm
on sc.support_id = scm.support_id
inner join @support_group scg
on sc.support_id = scg.support_id
INNER join @group_members gm
on scg.group_id=gm.group_id
INNER JOIN @Group_APplication ga
ON scg.group_id = ga.Group_ID
INNER JOIN @Application a
ON ga.App_ID = a.App_ID
WHERE ( ((scm.manager=@User_NM OR (@User_NM IS NULL OR @User_NM='admin')))
OR (gm.member_nm =@User_NM OR (@User_NM IS NULL OR @User_NM='admin')))

when a group member name is passed as value to @user_nm then his respective
group application name , group supervisor name , manager, support id should be displayed

when a manager name is passed as value to @user_nm then his respective
group application name , group supervisor name , manager, support id should be displayed

if user name is admin then all the values hould be displayed

the above query is not displaying any data.

Can anyone help me on this

Thanks before hand

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-18 : 07:20:37

because 'Aravind' is not mapped to a group application.

may be you meant this for your sample data,


insert into @group_application (group_id , app_id )select 1,'appl'
union all select 2,'app2'
union all select 2 3, 'app3'

With the above change, you should be able to see values.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-18 : 07:20:43

[code]
select distinct sc.support_id,scg.group_supervisor, scm.manager,a.appl_name
from @supportcenter sc
INNER join @supportmanager scm on sc.support_id = scm.support_id
INNER join @support_group scg on sc.support_id = scg.support_id
INNER join @group_members gm on scg.group_id=gm.group_id
LEFT JOIN @Group_APplication ga ON scg.group_id = ga.Group_ID
LEFT JOIN @Application a ON ga.App_ID = a.App_ID
WHERE ( ((scm.manager=@User_NM OR (@User_NM IS NULL OR @User_NM='admin')))
OR (gm.member_nm =@User_NM OR (@User_NM IS NULL OR @User_NM='admin')))
[/code]
there is no record for group_id =3 in @group_application table
so that records are not getting then use left join

swathigardas
Posting Yak Master

149 Posts

Posted - 2009-03-18 : 07:29:23

quote:

Originally posted by bklr


select distinct sc.support_id,scg.group_supervisor, scm.manager,a.appl_name
from @supportcenter sc
INNER join @supportmanager scm on sc.support_id = scm.support_id
INNER join @support_group scg on sc.support_id = scg.support_id
INNER join @group_members gm on scg.group_id=gm.group_id
LEFT JOIN @Group_APplication ga ON scg.group_id = ga.Group_ID
LEFT JOIN @Application a  ON ga.App_ID = a.App_ID
WHERE ( ((scm.manager=@User_NM OR (@User_NM IS NULL OR @User_NM='admin')))
OR (gm.member_nm =@User_NM OR (@User_NM IS NULL OR @User_NM='admin')))

there is no record for group_id =3 in @group_application table
so that records are not getting then use left join

i have executed the way u told. but when i give the value as
@user_nm='syam'

it is showing null for appl_name

where as syam is mapped to a group which is mapped to an application..

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-18 : 08:00:53

there is no value for group_id =3 in @group_application table
where as aravind is mapped to pallavi in @group_members table
so that ur getting the value as null for appl_name

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources