A Horrible (well, I Think So) Recursive Join...

Apr 2, 2008

Hi,

I have been trying to solve this issue for around 8 hours, now. Can someoone help me?

I have two tables:

Employee (
EMPLOYEE_ID INT PRIMARY KEY,
NAME VARCHAR(25)
)

E.G.:

1 Mark
2 Tracey
3 Jim

STRUCTURE (
SUPERVISOR_ID INT,
SUBORDINATE_ID INT
)

E.G.:

1 3
2 3

I need to get a query that gives me the following query:

SUPERVISOR_ID, SUPERVISOR_NAME, SUBORDINATE_ID, SUBORDINATE_NAME

I think that the SQL is going to go recursive and I think that this can be done reasonably easily (sadly, I have struck out so far), but how?

Thanks in advance,

QuietLeni

View 7 Replies

Recursive Join

Dec 3, 2006

CREATE TABLE EMPLOYEE(
Initialschar(3)Not Null,
EmpNamechar(25)Not Null,
CellPhonechar(20),
ExperienceLevel char(12)Not Null,
SupervisorInitials char(3)
CONSTRAINTEmployeePKPRIMARY KEY(Initials),
CONSTRAINTExperienceLevelCK CHECK (ExperienceLevel in
('Junior', 'Senior', 'Master', 'SuperMaster')),
CONSTRAINT SupervisorFK FOREIGN KEY (SupervisorInitials)
REFERENCES EMPLOYEE (Initials)
ON DELETE NO ACTION
ON UPDATE NO ACTION
);

INSERT INTO EMPLOYEE VALUES ('JLM', 'Joe Morrone', '(603) 489-2245', 'Master', NULL)
INSERT INTO EMPLOYEE VALUES ('BNF', 'Brenda Fowler', '(603) 467-8894', 'SuperMaster', NULL)
INSERT INTO EMPLOYEE VALUES ('JG', 'John Gromek', '(603) 678-9987', 'Junior', 'BNF')
INSERT INTO EMPLOYEE VALUES ('BEK', 'Barbara Kimball', '(603) 884-2312', 'Master', 'BNF')

Write a query which reports each employee's name and
-- experience level and the name of his/her supervisor

need help with this query,
the ouput is this ;

Employee ExperienceLevel Supervisor
------------------------- --------------- -------------------------
Barbara Kimball Master Brenda Fowler
John Gromek Junior Brenda Fowler

(2 row(s) affected)

Enhance your query to report all employees, whether or
-- or not they have a supervisor.
some help would be gratifull.

View 3 Replies View Related

Recursive Join

Jan 27, 2006

Hey all,
I am writing a procedure to perform a search against products in a website. It is a hierarchical setup, with websites, catalogs, category hierarchies, categories and products. So I need to get all the products that match the search, that are in categories that are in category hierarchies (category-to-category relationship), that are in catalogs that are in 1 website.

I can get the category that a matching product is in, but from there I need to recursive go up the ladder of category hierarchies until it reaches a point where the parent hierarchy is null, and then join that record with the catalogs that are in the 1 website. Here's what I thought might work:

Code:

SELECT ctlgs_CatalogCategoriesToProducts.*
FROM COMMERCE_PRODUCTS
INNER JOIN ctlgs_CatalogCategoriesToProducts ON COMMERCE_PRODUCTS.ITEMNO = ctlgs_CatalogCategoriesToProducts.ItemNo
INNER JOIN ctlgs_CatalogCategoryHierarchies ctlgs_CCH ON ctlgs_CatalogCategoriesToProducts.CategoryID = ctlgs_CatalogCategoryHierarchies.CategoryID

*** some recursive stuff***
WHILE (ctlgs_CatalogCategoryHierarchies.ParentHierarchyID IS NOT NULL)
BEGIN
INNER JOIN ctlgs_CCH ON ctlgs_CatalogCategoryHierarchies.ID = ctlgs_CCH.ParentHierarchyID
END

******

INNER JOIN ctlgs_Catalogs ON ctlgs_CatalogCategoryHierarchies.ID = ctlgs_Catalogs.RootHierarchyID
INNER JOIN ctlgs_CatalogsToWebsite ON ctlgs_Catalogs.ID = ctlgs_CatalogsToWebsite.CatalogID
INNER JOIN ctlgs_Websites ON ctlgs_CatalogsToWebsite.SiteID = ctlgs_Websites.ID
WHERE
ctlgs_Websites.ID = @website
AND COMMERCE_PRODUCTS.BLOCKED = 0
AND (
COMMERCE_PRODUCTS.ITEMNO LIKE @searchTerm
OR COMMERCE_PRODUCTS.DESCRIPTION LIKE @searchTerm
OR COMMERCE_PRODUCTS.DESCRIPTION2 LIKE @searchTerm
)

I've never used WHILE before, so I'm not sure what exactly you can put in there. Any advice would be greatly appreciated, thanks!

View 3 Replies View Related

Recursive Join

Jan 22, 2008

Hallo,

I have 2 Tables, a Customertable with "custid" as key and "custname" as customer name, and a second Table "CustomerHierarchy" with a "custid" field (which joins to customer) and a "custhierarchy" field which joins to "Custid" in the own table (CustomerHierarchy). When "custhierarchy" is Null, I'm on the top level of the hierarchy (the hierarchy builds Headquaters, Branches, Salesoffices, a.s.o).

I must construct a SSIS packet joining to each customer in the Customertable the "custname" for the highest hierarchy in the customerHierarchy Table. The custname is only in the Customertable.

How can I do this in a SSIS packet?

Thanks for your help
Hans

View 8 Replies View Related

Queries On Recursive Self Join Tables

Oct 24, 2007

Good morning!
Or good "whatever daytime you read this"!

SQL Server 2005 has this nice new feature Common table expression or CTE, which allows quite easy to define a "drill down" in recursive self join tables.

By recursive self join tables I mean this common example:
idPerson INT <--------|
idReportsTo INT ---------|
PersonName VARCHAR

A CTE to "go down" the tree from any entry point and find all subs to a parent entry is well documented. I managed to make myself a CTE and use it a lot!

What I find myself needing too often is:
a) Look up from a deep position and find the entry that is for example 3 steps above my reference in the branch
b) Look up from a deep position and find the one that is 2nd or 3rd level (absolute) from top of the tree in the branch

I did try quite some versions, but I cannot get it to work. Any idea how you do the "drill up" with a CTE or another SQL solution.
Of course performance is always needed, so I'd like to avoid the cursors I got it working with and use now. (It is not working good I admit...)

Cheers!
Ralf

View 7 Replies View Related

Transact SQL :: Types Don't Match Between Anchor And Recursive Part In Column ParentID Of Recursive Query

Aug 25, 2015

Msg 240, Level 16, State 1, Line 14

Types don't match between the anchor and the recursive part in column "ParentId" of recursive query "tmp". Below is query,

DECLARE @TBL TABLE (RowNum INT, DataId int, DataName NVARCHAR(50), RowOrder DECIMAL(18,2) NULL, ParentId INT NULL)
INSERT INTO @TBL VALUES
(1, 105508, 'A', 1.00, NULL),
(2, 105717, 'A1', NULL, NULL),
(3, 105718, 'A1', NULL, NULL),
(4, 105509, 'B', 2.00, NULL),
(5, 105510, 'C', 3.00, NULL),
(6, 105514, 'C1', NULL, NULL),

[code]....

View 2 Replies View Related

Horrible Table Needs Help

Feb 12, 2008

I'm having a problem normalizing this table that I have. I know I'm breaking the rules by having redundant data in the same table but I'm not sure how I'm going to calculate a penalty and find the total score. Can some lead me in the right direction fix this table for me?

Code Snippet
USE [aidms]
GO
/****** Object: Table [dbo].[PerfOrder] Script Date: 02/11/2008 16:40:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PerfOrder](
[PerfOrderID] [int] IDENTITY(1,1) NOT NULL,
[ShowID] [int] NOT NULL,
[DivisionID] [int] NOT NULL,
[OrganizationID] [int] NOT NULL,
[PerformanceShiftID] [int] NOT NULL,
[Cancelled] [bit] NOT NULL,
[DateRegistered] [datetime] NOT NULL,
[PerformanceTime] [datetime] NOT NULL,
[ReadyToBeJudged] [bit] NOT NULL,
[FirstJudgeID] [int] NULL,
[FirstCaptionID] [int] NULL,
[ScoreA1] [decimal](18, 0) NULL,
[ScoreB1] [decimal](18, 0) NULL,
[SecondJudgeID] [int] NULL,
[SecondCaptionID] [int] NULL,
[ScoreA2] [decimal](18, 0) NULL,
[ScoreB2] [decimal](18, 0) NULL,
[ThirdJudgeID] [int] NULL,
[ThirdCaptionID] [int] NULL,
[ScoreA3] [decimal](18, 0) NULL,
[ScoreB3] [decimal](18, 0) NULL,
[PenaltyScore] [decimal](18, 0) NULL,
[TotalScore] [decimal](18, 0) NULL,
CONSTRAINT [PK_PerfOrder] PRIMARY KEY CLUSTERED
(
[PerfOrderID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: ForeignKey [FK_PerfOrder_Captions] Script Date: 02/11/2008 16:40:55 ******/
ALTER TABLE [dbo].[PerfOrder] WITH CHECK ADD CONSTRAINT [FK_PerfOrder_Captions] FOREIGN KEY([FirstCaptionID])
REFERENCES [dbo].[Captions] ([CaptionID])
GO
ALTER TABLE [dbo].[PerfOrder] CHECK CONSTRAINT [FK_PerfOrder_Captions]
GO
/****** Object: ForeignKey [FK_PerfOrder_Captions1] Script Date: 02/11/2008 16:40:55 ******/
ALTER TABLE [dbo].[PerfOrder] WITH CHECK ADD CONSTRAINT [FK_PerfOrder_Captions1] FOREIGN KEY([SecondCaptionID])
REFERENCES [dbo].[Captions] ([CaptionID])
GO
ALTER TABLE [dbo].[PerfOrder] CHECK CONSTRAINT [FK_PerfOrder_Captions1]
GO
/****** Object: ForeignKey [FK_PerfOrder_Captions2] Script Date: 02/11/2008 16:40:56 ******/
ALTER TABLE [dbo].[PerfOrder] WITH CHECK ADD CONSTRAINT [FK_PerfOrder_Captions2] FOREIGN KEY([ThirdCaptionID])
REFERENCES [dbo].[Captions] ([CaptionID])
GO
ALTER TABLE [dbo].[PerfOrder] CHECK CONSTRAINT [FK_PerfOrder_Captions2]
GO
/****** Object: ForeignKey [FK_PerfOrder_Division] Script Date: 02/11/2008 16:40:56 ******/
ALTER TABLE [dbo].[PerfOrder] WITH CHECK ADD CONSTRAINT [FK_PerfOrder_Division] FOREIGN KEY([DivisionID])
REFERENCES [dbo].[Division] ([DivisionID])
GO
ALTER TABLE [dbo].[PerfOrder] CHECK CONSTRAINT [FK_PerfOrder_Division]
GO
/****** Object: ForeignKey [FK_PerfOrder_Judges] Script Date: 02/11/2008 16:40:57 ******/
ALTER TABLE [dbo].[PerfOrder] WITH CHECK ADD CONSTRAINT [FK_PerfOrder_Judges] FOREIGN KEY([FirstJudgeID])
REFERENCES [dbo].[Judges] ([JudgeID])
GO
ALTER TABLE [dbo].[PerfOrder] CHECK CONSTRAINT [FK_PerfOrder_Judges]
GO
/****** Object: ForeignKey [FK_PerfOrder_Judges1] Script Date: 02/11/2008 16:40:57 ******/
ALTER TABLE [dbo].[PerfOrder] WITH CHECK ADD CONSTRAINT [FK_PerfOrder_Judges1] FOREIGN KEY([SecondJudgeID])
REFERENCES [dbo].[Judges] ([JudgeID])
GO
ALTER TABLE [dbo].[PerfOrder] CHECK CONSTRAINT [FK_PerfOrder_Judges1]
GO
/****** Object: ForeignKey [FK_PerfOrder_Judges2] Script Date: 02/11/2008 16:40:58 ******/
ALTER TABLE [dbo].[PerfOrder] WITH CHECK ADD CONSTRAINT [FK_PerfOrder_Judges2] FOREIGN KEY([ThirdJudgeID])
REFERENCES [dbo].[Judges] ([JudgeID])
GO
ALTER TABLE [dbo].[PerfOrder] CHECK CONSTRAINT [FK_PerfOrder_Judges2]
GO
/****** Object: ForeignKey [FK_PerfOrder_Organization] Script Date: 02/11/2008 16:40:58 ******/
ALTER TABLE [dbo].[PerfOrder] WITH CHECK ADD CONSTRAINT [FK_PerfOrder_Organization] FOREIGN KEY([OrganizationID])
REFERENCES [dbo].[Organization] ([OrganizationID])
GO
ALTER TABLE [dbo].[PerfOrder] CHECK CONSTRAINT [FK_PerfOrder_Organization]
GO
/****** Object: ForeignKey [FK_PerfOrder_PerformanceShift] Script Date: 02/11/2008 16:40:59 ******/
ALTER TABLE [dbo].[PerfOrder] WITH CHECK ADD CONSTRAINT [FK_PerfOrder_PerformanceShift] FOREIGN KEY([PerformanceShiftID])
REFERENCES [dbo].[PerformanceShift] ([PerformanceShiftID])
GO
ALTER TABLE [dbo].[PerfOrder] CHECK CONSTRAINT [FK_PerfOrder_PerformanceShift]
GO
/****** Object: ForeignKey [FK_PerfOrder_Show] Script Date: 02/11/2008 16:40:59 ******/
ALTER TABLE [dbo].[PerfOrder] WITH CHECK ADD CONSTRAINT [FK_PerfOrder_Show] FOREIGN KEY([ShowID])
REFERENCES [dbo].[Show] ([ShowID])
GO
ALTER TABLE [dbo].[PerfOrder] CHECK CONSTRAINT [FK_PerfOrder_Show]
GO

View 2 Replies View Related

How To Convert Recursive Function Into Recursive Stored Procedure

Jul 23, 2005

I am having problem to apply updates into this function below. I triedusing cursor for updates, etc. but no success. Sql server keeps tellingme that I cannot execute insert or update from inside a function and itgives me an option that I could write an extended stored procedure, butI don't have a clue of how to do it. To quickly fix the problem theonly solution left in my case is to convert this recursive functioninto one recursive stored procedure. However, I am facing one problem.How to convert the select command in this piece of code below into an"execute" by passing parameters and calling the sp recursively again.### piece of code ############SELECT @subtotal = dbo.Mkt_GetChildren(uid, @subtotal,@DateStart, @DateEnd)FROM categories WHERE ParentID = @uid######### my function ###########CREATE FUNCTION Mkt_GetChildren(@uid int, @subtotal decimal ,@DateStart datetime, @DateEnd datetime)RETURNS decimalASBEGINIF EXISTS (SELECTuidFROMcategories WHEREParentID = @uid)BEGINDECLARE my_cursor CURSOR FORSELECT uid, classid5 FROM categories WHERE parentid = @uiddeclare @getclassid5 varchar(50), @getuid bigint, @calculate decimalOPEN my_cursorFETCH NEXT FROM my_cursor INTO @getuid, @getclassid5WHILE @@FETCH_STATUS = 0BEGINFETCH NEXT FROM my_cursor INTO @getuid, @getclassid5select @calculate = dbo.Mkt_CalculateTotal(@getclassid5, @DateStart,@DateEnd)SET @subtotal = CONVERT (decimal (19,4),(@subtotal + @calculate))ENDCLOSE my_cursorDEALLOCATE my_cursorSELECT @subtotal = dbo.Mkt_GetChildren(uid, @subtotal,@DateStart, @DateEnd)FROM categories WHERE ParentID = @uidENDRETURN @subtotalENDGORod

View 4 Replies View Related

Horrible Report Rendering Performance

Feb 11, 2008

Hello! We are having a strange problem with Reporting Services 2005. Our RS server is located in a DMZ with the supporting RS database housed in our internal network. We have a batch of reports that perform well when rendering to browsers in our internal network. However, when accessing these same reports externally, the reports take a very long time to render. The difference between internal vs. external is huge; we are looking at maybe a minute tops to render the report from an internal browser vs. 10-15 minutes from an external client browser. The website and reports are setup for anonymous access, so it shouldnt be an authentication problem.

The RS execution log doesnt indicate much difference between the two. The data retrieval does take a little longer (only a second or two), according to the log, for an external request. But still, the times recorded in the Execution Log dont match at all what we are experiencing for external requests.

The various logs, both for reporting services and IIS arent picking up any anomalies either and Im at my wits end as to what else I can use to try and troubleshoot this problem. Has anyone seen this behavior before, or have any ideas what might shed some light on this?

View 2 Replies View Related

Horrible Experience With Xp_cmdshell - Your Comments Please

May 21, 2008

I had a nerve-wracking experience with xp_cmdshell today.

In SSMS, I ran a command to "cd" to a directory, then "rename" some files in that directory. I made the ignorant mistake of running the commands separately - NEVER NEVER DO THIS.

What happened was, by the time I ran the "rename" command it defaulted to the "system32" directory and started renaming files there instead... talk about !!$#*@(!-up!

Has any ever done this, or heard about this horrible experience before? I am trying to document cases of this, so that I don't look like the complete idiot that I am.

View 2 Replies View Related

Horrible Performance Connecting To A Remote Sql Server...how Can I Fix It?

Apr 11, 2007

Hello, I have a sql server installed on a server in my server room.

The problem I have is that when I try to connect to the server, it takes a loooooooooong time to open the connection. Even when connecting through the Sql Server Management Studio it takes about 30 seconds just to open the connection.

Can anybody help me with some pointers on how to troubleshoot this issue? It only happens with this specific server

It might be a configuration issue, but where can I find information on how to configure it correctly?

View 2 Replies View Related

Can Any One Tell Me The Difference Between Cross Join, Inner Join And Outer Join In Laymans Language

Apr 30, 2008

Hello

Can any one tell me the difference between Cross Join, inner join and outer join in laymans language

by just taking examples of two tables such as Customers and Customer Addresses

Thank You

View 1 Replies View Related

Recursive SQL

Feb 9, 2007

I need to help in writing stored procedure to recursively delete Categories and ads for those categories
Simplified table views:
Category: CategoryID, Name, ParentCategoryID
Ads: AdID, Name, CategoryID
Please help

View 16 Replies View Related

How Can I Use Recursive CTE Here

Jun 1, 2007

Hello Everyone,
         I have a purchase order table that holds say 2 columns. PO and OrgPO. That is Purchase Order # and Original Purchase Order # respectively. Assume i have the following rows in the table.
PO         OrgPO
--          ------
po1        NULL
co1        po1
co2        co1
co3        co2
po2        NULL
cpo1      po2
po3        NULL
Now what i would like to report in the output is the PO and along with the lastly generated change order for that po. For eg,
PO     LastCO
--      ------
po1     co3
po2     cpo1
po3     po3
Currently i 'm using function to achieve this effect and i believe this is not the efficient way. I would like to generate this in a much efficient way. Please help me to achieve this.

View 2 Replies View Related

Recursive SQL

Jun 11, 2004

Hi

I have problem is getting list of all the Tree Level Employees.here is the my table structure

Manager ~ SubOrdinate
1 ~ 2
1 ~ 3
1 ~ 4
2 ~ 5
2 ~ 6
2 ~ 7
3 ~ 8
3 ~ 9
4 ~ 10
5 ~ 11
SO ON
I NEED THE WAY , HOW I CAN GET THE HIRE
LEVELS ( EG IF I PASS MANAGER(EMPLOYEE NO) 1 IT SHOULD DISPLAY HIS 1 LEVELS AND 2, 3 SO ON LEVELS OF LEVELS
OUTPUT COULD BE ,

PASSED EMPLOYEE NO : 1
LEVEL ~ EMPLOYEE
1 ~ 2
1 ~ 3
1 ~ 4
2.1 ~ 5
2.1 ~ 6
2.1 ~ 6
3.1 ~ 7
SO ON THAT EMPLOYEE RELATED INFORMATION
( ITS LIKE MLM LEVELS)
CAN ANYBODY HELP IN THE ,

THANKS

View 3 Replies View Related

Recursive Cte Within A Cte

May 12, 2006

is there any way to define a recursive cte within another cte. I have defined a recursive cte which works great for finding the different versions of a given item. What I would like to do is to define a set of items for which I want to find the different versions.

View 1 Replies View Related

How To Query The Using Recursive?

Jun 10, 2007

Hello,
I have the following tables:

Article(articleID,CategoryID,ArticleTitle)
Categories(categoryID,ParentID,CategoryTitle)
I am trying to retrieve the main category ID for a specific article ID.
For example lets say I have this data:
Article:

1, 10 , "some title"
2,10,"some title"
3,11,"some title"
Categories:
1, NULL , "some title"
2, 1, "some title"
10, 2, "some title"
11, 10 , "some title"

In this example I want to know who is the main category of article 3.
The query should return the answer: 1
Thats because:

The article ID 3 is inside category 11.
Parent for category 11 is 10.
Parent for category 10 is 2.
Parent for category 2 is 1
and Parent for category 1 is NULL, which means category 1 has no parents and it is the main category.
Query will return article id, category id, main_category_id, ArticleTitle, CategoryTitle (some union between 2 tables)
Do you have any suggestions for such query?
Thanks all.

View 1 Replies View Related

Recursive Query ..

Jan 18, 2008

Recursive quey to show products with "custom defines fields" related by Classifications, instead of per product Hello, I’m working on a project .. .

I’m desperating due to the
complex (for me and also I think for some others) sql query that I need to
write, to show the products with his “custom defined fields�? that are inside a ProductsFieldsByClassification
table that holds this mentioned “custom defined fieds�? according to the Classifications
table, on where the Products can be found trought the productsClassifications
table.

CustomFields can be defined
and set for the products, trought his Classifications (instead of define a
custom field for each product (that consume a lot of data), I decide to use it as
I explain)

I will to know the properly
SQL QUERY to show a list of products with the ProductsFieldsByClassifications and ProductsFieldsValuesByClassifications:

As example on
a Requested ID_Classification = 16 (Torents/Games/Dreamcast/PAL), the products must be show with the
ProductsFields and Values that has the DBA for the:

·
requested
ID_Classification

o
PAL (ID_Classification: 16)

·
AND all
the Classifications that belongs above (trought ID_ParentClassification) that are :

o
Torrents (ID_Classification:
1) that will show the products values for the “Size�?

o
Games (ID_Class..:4) ß this classification has no CustomFields so none from this one.

o
Dreamcast (ID_Class..:14 )
that will show his ID_Classification(14) product field “Levels�? value (but not “AllowSave�? as not have value for any product)

Hmnn i show a graphic that i design for (feel to click over to see at correct resolution)

I also write asp.net tutorials. For those interested see my blog at http://abmartin.wordpress.com

View 2 Replies View Related

Recursive Tsql

Feb 26, 2008

i have a table like this
parentid |    childid    |   description
1             2                     blah
1              3
1              4
2              23
2              24
5              8
3              10
and i want to give the parentid 1 and get all the children
i have a cursor now like this but i dont know how to make it recursive any help?

1
2 DECLARE @childid nvarchar(50)
3 DECLARE ItemStruc CURSOR FOR
4
5 SELECT cmponent_prt_no , parent_part_no
6 FROM oauser_prod_structure
7 WHERE parent_part_no = @parentid
8
9 OPEN ItemStruc
10 FETCH NEXT FROM ItemStruc
11 INTO @childNum , @parentid
12
13 WHILE @@FETCH_STATUS = 0
14 BEGIN
15
16 print @childNum +'is the a child to: ' +@parentid

View 12 Replies View Related

Recursive SQL Query

Sep 21, 2004

I have a tough issue with a query.

I have the following structure

Table: Users

UserId ParentUserId
1 1
2 1
3 2
4 1
5 4
6 5

I need to write a stored procedure that takes in UserId as parameter and returns everyone under him/her.

So if Param=1 it would return result set of 2,3,4,5,6

Anyone done this before or have any ideas?

Thanks,
ScAndal

View 1 Replies View Related

Recursive Query Help

Jul 20, 2005

Hi,

Does anyone know how to do an sql recursion queries?

I believe it involves a view with a union.

I have a User Table and in that table i have a employee_id and a
boss_id. What i'd like to do is to find all employees under a certain
boss.
For example,

Employee_ID Boss_ID
1
2                     1
3
4                     3
5                     2

So if i'd like to know who are under the employee_id = 1 it will return
employee_id 2 and 5 since employee 2 also is the boss of employee_id =
5.

To do that i'd have to have recursion query.

Thanks,

View 2 Replies View Related

Recursive Triggers

Dec 14, 2000

I'm using exec sp_dboption 'ilgadmin', 'recursive triggers',true

I made

create trigger
dbo.templates_ondelete
on templates for delete
as
begin
delete pages
where templateid = (select [id] from deleted)
end
go

create trigger
dbo.pages_ondelete
on pages for delete
as
begin
delete pageItems
where pageid = (select [id] from deleted)
end
go

The second trigger must be started by the first one.

But it doens't work because if I do
delete from templates where id = 2 more than one page (7 pages) are deleted and the second trigger doesn't work (it can only delete one by one in pageitems!)

Is there any option in SQL Server that I forgot or can I use an other methode

Best regards
Axel

View 2 Replies View Related

Infinite Recursive?

Nov 24, 1999

View 1 Replies View Related

Recursive Search

Jun 5, 2002

Hi,
I have a table with 2 fields in it Circuit_ID and Parent_Circuit_ID and I'd like to pass a stored procedure a circuit_ID and get a resultset back of all Circuit_ID's that are associated with the original.

That means it needs to look at each record and do another search on all circuits that have that parent ID, recursively.

Does anyone have any good ideas on the best way to do this.

Appreciate any help.

Steve

View 1 Replies View Related

Help With Recursive Function

Nov 1, 2007

I have a table of product types, the table is layed like this:

TypeID
TypeName
ParentID

each type has a ParentID equal to one of the other types TypeID (except the very top level types) to build a hierarchy with several levels.

I need a way of giving a function a single TypeID, and have it return a table that has the the TypeID of every type underneath it in the hierarchy. it could go down several levels (like 10 or more) so it needs to recursively work...

I'm just completely lost...

I can get this to work just fine in VB... but in VB i can just use a nice little for each loop...

any ideas?

View 1 Replies View Related

Recursive Function

Feb 1, 2005

I am trying to write a recursive function which allows me to provide a hierarchical structure from an employee table but I am getting an error

Server: Msg 512, Level 16, State 1, Procedure GetEmployee, Line 10
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Is there a way in which I can write a recursive function to do this. Or would I need to use cursors, multiple functions???
Any help or examples would be great. Thanks for your help

CREATE FUNCTION GetEmployee(@EmpID as int)
RETURNS @Employee Table
(
StaffNo int,
ParentStaffNo int
)
AS
BEGIN
DECLARE @ManagerID int
SET @ManagerID = (SELECT [id] FROM eii.dbo.test WHERE Parent_ID = @EmpID)

IF @ManagerID IS NOT NULL and @ManagerID > '' and @ManagerID <> @EmpID
INSERT INTO @Employee
SELECT [id],parent_id FROM eii.dbo.test Where [id] = @ManagerID
UNION
SELECT * FROM GetEmployee(@ManagerID)

RETURN
END

View 1 Replies View Related

Recursive Queries

Jul 13, 2004

I have a following structure

table A
userid ReferedBy
BA
C B

Table B
Userid compID
AAlpha
Bself
Cself

now the scenario is :
the user A is from the company "Alpha"
he introduces user B, who registers in the system his company bcomes "self",
now B inturn refers user C who also registers in the system and his company is now again "self".
Now I need to generate a report of number of users that have registered under one company, for eg.
for the company "Alpha" no of users becomes 2 since A refered to two users and both of them have registered.

I m stuck with the query.
thanks in advance...
regards,
Harshal

View 14 Replies View Related

Recursive Cursor

Sep 23, 2004

Hi,

I have 2 tables

tblparent

parent_term_id term_id
-------------- -----------
1 2
2 3

tblname

id name
----- ------------------------------------------
1 My top parent node
2 My second node
3 my child node

If I do a search for say 'my child node' I need to display where 'my child node' is in relation to the hierarchy. i.e i need to show it's parent and if that has a parent I need to show its parent etc... and continue until there are no more parents left

So using the table details if i search for 'my child node'

I need to display this :
My top parent node -> My second node - > my child node

The id for 'My top parent node' doesn't exist in tblparent because it is the top parent

Can anybody help with doing this

Thanks in advance

View 7 Replies View Related

Recursive Sql Query

Sep 29, 2004

View 7 Replies View Related

Can A Recursive Query Do This?

Dec 23, 2004

I am wondering if there is some type of recursive query to return the values I want from the following database.

Here is the setup:

The client builds reptile cages.

Each cage consists of aluminum framing, connectors to connect the aluminum frame, and panels to enclose the cages. In the example below, we are not leaving panels out to simplify things. We are also not concerned with the dimensions of the cage.

The PRODUCT table contains all parts in inventory. A finished cage is also considered a PRODUCT. The PRODUCT table is recursively joined to itself through the ASSEMBLY table.

PRODUCTS that consist of a number of PRODUCTS are called an ASSEMBLY. The ASSEMBLY table tracks what PRODUCTS are required for the ASSEMBLY.

Sample database can be downloaded from http://www.handlerassociates.com/cage_configurator.mdb

Here is a quick schema:

Table: PRODUCT
--------------------------
PRODUCTIDPK
PRODUCTNAMEnVarChar(30)

Table: ASSEMBLY
--------------------------
PRODUCTIDPK(FK to PRODUCT.PRODUCTID)
COMPONENTIDPK(FK to PRODUCT.PRODUCTID)
QTYINT

I can write a query that takes the PRODUCTID, and returns all

PRODUCT
=======
PRODUCTIDPRODUCTNAME
--------------------
1Cage Assembly - Solid Sides
2Cage Assembly - Split Back
3Cage Assembly - Split Sides
4Cage Assembly - Split Top/Bottom
5Cage Assembly - Split Back and Sides
6Cage Assembly - Split Back and Top/Bottom
7Cage Assembly - Split Back and Sides and Top/Bottom
833S - Aluminum Divider
933C - Aluminum Frame
10T3C - Door Frame
11Connector Kit
12Connector Socket
13Connector Screws

ASSEMBLY
=========
PRODUCTIDCOMPONENTQTY
---------------------
198
1104
1111
211
281
311
381
411
481
511
582
611
682
711
783
11128
11138

I need a query that will give me all parts for each PRODUCT.

Example: I want all parts for the PRODUCT "Cage Assembly - Split Back"

The results would be:

PRODUCTIDPRODUCTNAME
--------------------
2Cage Assembly - Split Back
1Cage Assemble - Solid Back
933C - Aluminum Frame
10T3C - Door Frame
11Connector Kit
833S - Aluminum Divider
12Connector Socket
13Connector Screws

Is it possible to write such a query or stored procedure?

View 4 Replies View Related

Recursive Triggers

Jan 7, 2004

use pubs
sp_configure 'nested triggers',1
go
reconfigure
go
alter database pubs
set RECURSIVE_TRIGGERS ON
go
create table abcd (recid int)
go
create trigger abcd_trigger
on abcd
instead of insert
as
begin
declare @recid int
select @recid = recid + 1 from inserted
insert into abcd values (@recid)
end
go
insert into abcd values (1)
go
select * from abcd
go
drop trigger abcd_trigger
drop table abcd
go

Why does this insert value as 2 even though I have enabled recursive triggers.... Gurus .. any answers????

And as for changing the database options ... please check what they are before executing this post so that you can reset them later.

View 2 Replies View Related

Recursive Cursors

May 16, 2006

Hi,
Is there any recursive cursors in sqlserver?If any then what are recursive cursors?
Thanks in advance

View 3 Replies View Related