MS SQL

Connect by prior command

Please support our MS SQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Nov 2009

Posts: 1

Reputation: sk.faheemuddin is an unknown quantity at this point

Solved Threads: 0

sk.faheemuddin sk.faheemuddin is offline

Offline

Newbie Poster

Connect by prior command

Nov 6th, 2009

Hi All,
Can anybody give me a solution how to solve or write a query for this scenario?

there is table called ITT1 with the columns

FATHER, CHILD_NUM, CODE, CODE_QTY
a 1 b 1
b 1 c 1.5
c 1 d 2
d 1 e 1

There are 3 columns father,child_num,code & the code_quantity.
It is explained as 'a' is my FINAL finished good and is produced from raw material 'b' using 1kg.
and here 'b' becomes my finished good and is produced from raw material 'c' using 1kg.
and here 'c' becomes my finished good and is produced from raw material 'd' using 1kg.
and here 'd' becomes my finished good and is produced from raw material 'e' using 1kg.

now i want how much raw material 'e' is required to produced for eg. 20kg of 'a'

should i use connect by prior or sub queries will help in this regards.
kindly advice.

Regards,
Faheem

•

Join Date: Sep 2009

Posts: 557

Reputation: network18 is an unknown quantity at this point

Solved Threads: 64

network18

Offline

Posting Pro

Nov 10th, 2009

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
SELECT code_qty *10 FROM ITT1 WHERE code=
(SELECT father FROM ITT1 WHERE code=
	(SELECT father FROM ITT1 WHERE code = 
		(SELECT father FROM ITT1 WHERE code = 'a')
	)
)
select code_qty *10 from ITT1 where code=
(select father from ITT1 where code=
	(select father from ITT1 where code = 
		(select father from ITT1 where code = 'a')
	)
)