InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 20 May 2013 06:56:54 GMT20http://www.sqlservercentral.com/Forums/Topic626425-338-1.aspx[quote][b]noeld (12/29/2008)[/b][hr]I know I am going to get some heat because of it but hey USA is a free country.[/quote]Only if you've never had to fight for her... ;)Mon, 29 Dec 2008 18:48:58 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic626425-338-1.aspxThank 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:Mon, 29 Dec 2008 14:18:02 GMTChris Harshmanhttp://www.sqlservercentral.com/Forums/Topic626425-338-1.aspxYou'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 [i]expert[/i]." I want to have some sense that they are at least trying to solve the problem [i]themselves[/i] and are just looking for a little help to get past a stumbling block.Mon, 29 Dec 2008 14:04:35 GMTThe Dixie Flatlinehttp://www.sqlservercentral.com/Forums/Topic626425-338-1.aspx[quote][b]Matt Miller (12/29/2008)[/b][hr]That's ok Noel - Chris harshman already posted something similar on the OTHER thread the OP started on the same topic....:[url=http://www.sqlservercentral.com/Forums/Topic626424-360-1.aspx]http://www.sqlservercentral.com/Forums/Topic626424-360-1.aspx[/url]enjoy![/quote]: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.Mon, 29 Dec 2008 13:47:39 GMTChris Harshmanhttp://www.sqlservercentral.com/Forums/Topic626425-338-1.aspx[quote][b]Bob Hovious (12/29/2008)[/b][hr]At least this one didn't have URGENT URGENT URGENT in the topic header. :P[/quote]Good Point!Mon, 29 Dec 2008 11:20:23 GMTnoeldhttp://www.sqlservercentral.com/Forums/Topic626425-338-1.aspxAt least this one didn't have URGENT URGENT URGENT in the topic header. :PMon, 29 Dec 2008 11:12:09 GMTThe Dixie Flatlinehttp://www.sqlservercentral.com/Forums/Topic626425-338-1.aspxHa! I didn't follow the link so I have a deserved "Ooops!" Cross-posting hits again!Mon, 29 Dec 2008 10:46:15 GMTnoeldhttp://www.sqlservercentral.com/Forums/Topic626425-338-1.aspxThat's ok Noel - Chris harshman already posted something similar on the OTHER thread the OP started on the same topic....:[url=http://www.sqlservercentral.com/Forums/Topic626424-360-1.aspx]http://www.sqlservercentral.com/Forums/Topic626424-360-1.aspx[/url]enjoy!Mon, 29 Dec 2008 10:43:38 GMTMatt Miller (#4)http://www.sqlservercentral.com/Forums/Topic626425-338-1.aspxI know I am going to get some heat because of it but hey USA is a free country.[code]declare @user_id intselect @user_id = 2;with cteAS(select b.id, b.parent_id, b.name as bunit, w.id as wid, w.name as wgrpfrom 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.wgidwhere uw.uid = @user_id union allselect b.id, b.parent_id, b.name as bunit, w.id as wid, w.name as wgrpfrom bizunit b inner join cte on b.parent_id = cte.id inner join workgroup w on cte.wid = w.id)select bunit, wgrpfrom cteorder by bunit, wgrp[/code]Mon, 29 Dec 2008 10:41:41 GMTnoeldhttp://www.sqlservercentral.com/Forums/Topic626425-338-1.aspxNow - 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....:)Mon, 29 Dec 2008 10:31:59 GMTMatt Miller (#4)http://www.sqlservercentral.com/Forums/Topic626425-338-1.aspxApplause... thunderous applause.Mon, 29 Dec 2008 10:24:26 GMTThe Dixie Flatlinehttp://www.sqlservercentral.com/Forums/Topic626425-338-1.aspxThis looks like a homework assignment, and we don't like solving homeworks for others.See example D in [url=http://msdn.microsoft.com/en-us/library/ms175972.aspx]http://msdn.microsoft.com/en-us/library/ms175972.aspx[/url] and try to modify it yourself to fit your purpose.Razvan SocolSQL Server MVPMon, 29 Dec 2008 03:44:13 GMTRazvan Socolhttp://www.sqlservercentral.com/Forums/Topic626425-338-1.aspxTable: workgroup id - primary keyid, name== ====1 w12 w23 w3Table: bizunit : id - primary key ; parent_id - foreignkey to same table column Idid, name, parent_id== ====== ========1 b1 0 2 b2 13 b3 14 b4 25 b5 46 b6 37 b7 0Table: Users id- primary keyid, name1 u12 u2Table: workgroup_bizunit (wgid,bid) --> primary keywgid, bid==== ===2 23 22 3Table: users_workgroup (uid,wgid) - primary keyuid, wgid=== =====1 12 22 3The 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 | |________b6The Output Should beb2, w2b2, w3b3, w2b4, w2b4, w3b5, w2b5, w3b6, w2I 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 queryMon, 29 Dec 2008 00:15:25 GMTnag_arjun123