please give a recursive query for my problem..

Question

Post reply

please give a recursive query for my problem..

Shhash

Newbie

Points: 4
More actions
May 15, 2009 at 7:35 pm

#72740

I've compiled the question onto a image, just to explain it clearly and here are the links
http://img507.imageshack.us/img507/3972/pleasehelp.jpg
or
http://i42.tinypic.com/34tdawh.jpg
or
http://img29.imageshack.us/img29/3972/pleasehelp.jpg
I'm using microsoft sql 2000.
this was my assignment as a part of learning temporary table, declaring variables,and using operators.
please help me, I'm newbie for database.. any help will be appreciated..thanks in advance

Viewing 9 posts - 1 through 9 (of 9 total)

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

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 1

See the following links

http://www.mssqltips.com/tip.asp?tip=938

http://www.intelligententerprise.com/001020/celko1_1.jhtml

--Ramesh

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

As this is an assignment, I'd like to suggest that you show us what you have done so far to try and solve your problem. From there we can offer you suggestions that may help further your understanding and help you with your assignment.

Shhash Newbie Points: 4 More actions · Answer 3

@ramesh-2 thanks a lot, that was very informative.

@lynn pettis

I've created a table as

create database bom

use bom

create table bom (item_id int, item_name varchar(15), qty int, parent varchar(10),parent_id int, has_child int, primary key(item_id));

and has inserted the following values.

insert into bom values (1,'xyz',1,NULL,NULL,1);

insert into bom values (2,'a',4,'xyz',1,1);

insert into bom values (3,'b',3,'xyz',1,1);

insert into bom values (4,'c',6,'xyz',1,1);

insert into bom values (5,'m',5,'a',2,0);

insert into bom values (6,'n',4,'a',2,0);

insert into bom values (7,'p',3,'b',3,0);

insert into bom values (8,'q',4,'b',3,1);

insert into bom values (9,'r',8,'b',3,0);

insert into bom values (10,'s',5,'c',4,0);

insert into bom values (11,'t',2,'c',4,0);

insert into bom values (12,'m',3,'q',8,0);

insert into bom values (13,'t',6,'q',8,0);

insert into bom values (14,'n',1,'t',4,11);

the tree can be graphically represented as

http://i39.tinypic.com/2d8517d.jpg

or

http://img196.imageshack.us/img196/3300/mytree.jpg

or

and querying the following to get the leaf nodes of the tree

select item_name, qty from bom where has_child=0

but my problem is to list all the leaf nodes of any intermediate node, I've implemented the same in .NET but our professor wants me to implement in sql or oracle, he is insisting me to learn database static coding in sql and other databases :hehe:

I guess I presented my problem in understandable way here, now I'm learning DBMS as a part of my curriculum; pretty new to this, any help is appreciated. thanks

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

The only two things missing from the above is your expected results and the code you have developed so far. Sorry, but the graph and your verbal description just aren't enough. Based on the sample data you provided, what are you trying to return? What have you written so far to solve the problem?

Shhash Newbie Points: 4 More actions · Answer 5

what I expect is to write a query which returns all the leaf nodes, for example

with reference to above tree..

If I query the leaf nodes of 'A' it should display M and N,

for B it should give me P, M and T,

and for C it should give S and N as leaf nodes,

similarly for T it should give N as leaf node.

i.e I want the query to return the extreme/last child nodes in the hierarchy.

Greg Snidow SSCoach Points: 16108 More actions · Answer 6

Check out Books On Line for "expanding heirarchies"

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

First, your data provided does not match the diagram.

Here is some code to play with:

create table dbo.bom (item_id int, item_name varchar(15), qty int, parent varchar(10),parent_id int, has_child int, primary key(item_id));

insert into dbo.bom values (1,'xyz',1,NULL,NULL,1);

insert into dbo.bom values (2,'a',4,'xyz',1,1);

insert into dbo.bom values (3,'b',3,'xyz',1,1);

insert into dbo.bom values (4,'c',6,'xyz',1,1);

insert into dbo.bom values (5,'m',5,'a',2,0);

insert into dbo.bom values (6,'n',4,'a',2,0);

insert into dbo.bom values (7,'p',3,'b',3,0);

insert into dbo.bom values (8,'q',4,'b',3,1);

insert into dbo.bom values (9,'r',8,'b',3,0);

insert into dbo.bom values (10,'s',5,'c',4,0);

insert into dbo.bom values (11,'t',2,'c',4,0);

insert into dbo.bom values (12,'m',3,'q',8,0);

insert into dbo.bom values (13,'t',6,'q',8,0);

insert into dbo.bom values (14,'n',1,'t',4,11);

select * from dbo.bom;

select

bom1.*

from

dbo.bom bom1

left outer join dbo.bom bom2

on (bom1.item_id = bom2.parent_id)

where

bom2.item_id is null

order by

bom1.item_id

;

drop table dbo.bom;

Shhash Newbie Points: 4 More actions · Answer 8

@ Greg

thanks man, this is what I was exactly searching for.. "expanding hierarchies". Thanks you very much, I guess my problem is solved.

@ Lynn Pettis

thanks for helping out, I'll come back if I get any more doubts.