Help running a query please - 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

Help running a query please

Author

Topic

rengal
Starting Member

8 Posts

Posted - 2008-04-10 : 05:59:15

Hi guys. I'm looking for help to run a query that should come out looking like this


unit                        reports_to

Development Dept            Representative Div
Systems Group               Development Dept
Design Section              Systems Group
Production Spec Section     Systems Group
Proposal Section            Systems Group

This is the table i have to work from


UNITNO	ORGNAME	                PARENT	BUDGET
2000	Representative Div	1000	459000.0000
2100	Development Dept	2000	391000.0000
2110	Systems Group	        2100	332000.0000
2111	Proposal Section	2110	87000.0000
2112	Design Section	        2110	132000.0000
2115	Production Spec Sect	2110	68000.0000

I can't see any way of doing this query though

I know the PARENT is the reports_to column and that the UNITNO is the Department but can't find any way to run the query. Hoping you guys can help

Thanks

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-10 : 06:08:26

Are you using SQL 2005 or 2000?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

rengal
Starting Member

8 Posts

Posted - 2008-04-10 : 06:12:12

I've been trying now on and off for a week to get it to run

Using SQL 2005

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-10 : 06:34:07

May be this:

-- Prepare sample data
create table #t
(
	UNITNO int,
	ORGNAME varchar(50),
	PARENT int,
	BUDGET numeric(12,4)
)

insert #t
select 2000, 'Representative Div', 1000, 459000.0000 union all
select 2100,'Development Dept',2000, 391000.0000 union all
select 2110,'Systems Group',2100, 332000.0000 union all
select 2111, 'Proposal Section',2110, 87000.0000 union all
select 2112, 'Design Section', 2110, 132000.0000 union all
select 2115, 'Production Spec Sect', 2110, 68000.0000
GO

-- Main query to generate desired output
with cte_test(UnitNo, Parent)
as
(
Select UnitNo, Parent from #t where Parent = 1000
union all
select t1.UnitNo, t1.Parent 
from #t t1 join cte_test t2 on t1.Parent = t2.UnitNo
)
Select t1.orgname, t2.orgname
from cte_test c join #t t1 on c.unitno = t1.unitno
join #t t2 on c.parent = t2.unitno

drop table #t
GO

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-10 : 06:34:08

Use a recursive CTE. There are examples in Books Online.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-10 : 06:38:50

My mistake. It is far more simpler than that.
Using Harsh's sample code, try this

SELECT		x.OrgName AS Unit,
		s.OrgName AS Reports_To
FROM		#t AS s
INNER JOIN	#t AS x ON x.Parent = s.UnitNo

E 12°55'05.25"
N 56°04'39.16"

rengal
Starting Member

8 Posts

Posted - 2008-04-10 : 07:35:59

Ok, thanks guys. Will try now

rengal
Starting Member

8 Posts

Posted - 2008-04-10 : 08:33:11

Ok, it didn't work

Don't have permissions to change the table, only run queries on it

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-10 : 08:36:13

Who told you to change the table? Just run the SELECT query Peso provided.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-10 : 08:37:16

What?

For testing Harsh's suggstion, you should only run

-- Main query to generate desired output
with cte_test(UnitNo, Parent)
as
(
Select UnitNo, Parent from YourTableNameHere where Parent = 1000
union all
select t1.UnitNo, t1.Parent 
from YourTableNameHere t1 join cte_test t2 on t1.Parent = t2.UnitNo
)
Select t1.orgname, t2.orgname
from cte_test c join YourTableNameHere t1 on c.unitno = t1.unitno
join YourTableNameHere t2 on c.parent = t2.unitno

To test my suggestion, you should only run

SELECT		x.OrgName AS Unit,
		s.OrgName AS Reports_To
FROM		YourTableNameHere AS s
INNER JOIN	YourTableNameHere AS x ON x.Parent = s.UnitNo

E 12°55'05.25"
N 56°04'39.16"

rengal
Starting Member

8 Posts

Posted - 2008-04-10 : 09:07:20

Just seen ur new post Peso. Thanks

EDIT: It's working now. Thanks v.much guys

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources