Require help on CTE common table expressions query construncting

Question

Require help on CTE common table expressions query construncting

nag_arjun123

SSC Rookie

Points: 48
More actions
December 29, 2008 at 12:15 am

#197507

Table: workgroup id - primary key
id,name
== ====
1 w1
2 w2
3 w3
Table: bizunit : id - primary key ; parent_id - foreignkey to same table column Id
id, name, parent_id
================
1b10
2b21
3b31
4b42
5b54
6b63
7b70
Table: Users id- primary key
id,name
1u1
2u2
Table: workgroup_bizunit (wgid,bid) --> primary key
wgid,bid
=======
22
32
23
Table: users_workgroup (uid,wgid) - primary key
uid,wgid
========
11
22
23
The above given are the table structure and having the sample data.
Now , i have to construct a query based on these tables . could anyone help regarding this.
The input parameter would be the User Id : Lets say I want the details of business units, workgroup which are associated to User Id - 2
In the application The business units tree view structure would be like this
--------------------------------------------------------------------------
Business unit b2 is associated to workgroups w2,w3. And User Id 2 is associated to workgroups w2,w3.
Business unit b3 is associated to workgroup w2.
System
|
|________b2
| |________b4
| |________b5
|
|________b3
|
|________b6
The Output Should be
b2, w2
b2, w3
b3, w2
b4, w2
b4, w3
b5, w2
b5, w3
b6, w2
I want business units b4,b5 in the output because associated to its workgroup thru parent business unit b2.
I want business units b6 in the output because associated to its workgroup thru parent business unit b3.
I have to user CTE common table expressions. Please anyone help regarding this query

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Razvan Socol SSCarpal Tunnel Points: 4811 More actions · Answer 1

This looks like a homework assignment, and we don't like solving homeworks for others.

See example D in http://msdn.microsoft.com/en-us/library/ms175972.aspx and try to modify it yourself to fit your purpose.

Razvan Socol

SQL Server MVP

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 2

Applause... thunderous applause.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

Now - keep in mind this: should you TRY to modify the code listed above, and you get stuck somewhere, and you were to come back and post what you tried (and you make it look like it's NOT homework)... You will likely get quite a bit of help....:)

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

noeld SSC Guru Points: 96590 More actions · Answer 4

I know I am going to get some heat because of it but hey USA is a free country.

declare @user_id int

select @user_id = 2

;with cte

AS

(

select b.id, b.parent_id, b.name as bunit, w.id as wid, w.name as wgrp

from bizunit b

inner join

workgroup_bizunit wb on b.id = wb.bid

inner join

workgroup w on wb.wgid = w.id

inner join

users_workgroup uw on uw.wgid = wb.wgid

where uw.uid = @user_id

union all

select b.id, b.parent_id, b.name as bunit, w.id as wid, w.name as wgrp

from bizunit b

inner join cte

on b.parent_id = cte.id

inner join

workgroup w on cte.wid = w.id

)

select bunit, wgrp

from cte

order by bunit, wgrp

* Noel

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 5

That's ok Noel - Chris harshman already posted something similar on the OTHER thread the OP started on the same topic....:

http://www.sqlservercentral.com/Forums/Topic626424-360-1.aspx

enjoy!

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

noeld SSC Guru Points: 96590 More actions · Answer 6

Ha! I didn't follow the link so I have a deserved "Ooops!" Cross-posting hits again!

* Noel

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 7

At least this one didn't have URGENT URGENT URGENT in the topic header. 😛

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

noeld SSC Guru Points: 96590 More actions · Answer 8

Bob Hovious (12/29/2008)
At least this one didn't have URGENT URGENT URGENT in the topic header. 😛

Good Point!

* Noel

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 9

Matt Miller (12/29/2008)
That's ok Noel - Chris harshman already posted something similar on the OTHER thread the OP started on the same topic....:
http://www.sqlservercentral.com/Forums/Topic626424-360-1.aspx
enjoy!

:rolleyes: Oh well, I just happened to see the other "original" post first, didn't realize this was cross posted in this forum. Also guess I give people too much the benefit of the doubt that this was just a simplification of an actual problem they were trying to solve as opposed to a homework assignment.

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 10

You're a good soul, Chris Harshman 🙂

Maybe I'm just getting cynical, but I'm finding myself less and less willing to help people who are just throwing up a problem and saying "I need someone to write this query for me." It doesn't matter if they try to be flattering and say "I need help from an expert." I want to have some sense that they are at least trying to solve the problem themselves and are just looking for a little help to get past a stumbling block.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 11

Thank you Bob!

I understand why people don't want to just do other people's work, there are times in these forums I wonder if the original posters ever even heard of Books Online or MSDN. I understand also why people typically request the questions be posted in a specific way, with CREATE TABLE... INSERT... instead of just listing the contents of the table like this post did. I remember the first time I worked with a recursive CTE though, and they do take some getting used to. Even though I've written recursive queries before on Oracle with the CONNECT BY syntax. Or maybe I'm just not challenged enough at my current job and need to feel usefull :hehe:

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 12

noeld (12/29/2008)
I know I am going to get some heat because of it but hey USA is a free country.

Only if you've never had to fight for her... 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)