Complex Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Complex Query

Author

Topic

erwine
Yak Posting Veteran

69 Posts

Posted - 2007-04-03 : 03:59:09

Hi all,

I have these two tables:
TABLE1:
ProductNo. Component
CH-001 A100
CH-001 B100
CH-001 C100

TABLE2:
Parent UniqueNo. Order ItemCode
A100 1 1 AK
A100 2 2 AL
A100 3 3 AM
A100 4 4 OO

AK 5 1 AM
AK 6 2 OO
AK 7 3 UK

B100 8 1 BS
B100 9 2 BT
B100 10 3 AL
B100 11 4 OO

C100 12 1 OO
C100 13 2 KO

D100 14 1 DP
D100 15 2 OO

which means that product CH-001 is made of components A100,B100 & C100. and the components are also made from sub-components. It is stored in two tables only.

is there any way that I can write a SQL statement to have a list like:
Product No. Component UniqueNo Order ItemCode
CH-001 A100 4 4 OO
CH-001 AK 6 2 OO
CH-001 B100 11 4 OO
CH-001 C100 12 1 OO

Note: Notice that i only interested in all item OO used to produce CH-001 (that's why component D100 is not included) and the list is sorted in such a way that it will go from product to component and to sub-component and so on, then back to component.

I dont want to use cursor to loop. Do you guys have any idea to write a join statement for this?

Thanks for your kind attention.

Cheers,
erwine

... sql is fun...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-03 : 04:14:50

how many levels parent - child do you have ? Is there a max ?

erwine
Yak Posting Veteran

69 Posts

Posted - 2007-04-03 : 04:17:19

Hi KHTan,

Thanks for replying.

Current situation: the maximum is only 3 levels. However, it is very possible to have more than 3 levels in the future.

cheers,
erwine

... sql is fun...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-03 : 04:40:02

[code]-- Prepare sample data
DECLARE @Table1 TABLE (ProductNo VARCHAR(6), Component VARCHAR(4))

INSERT @Table1
SELECT 'CH-001', 'A100' UNION ALL
SELECT 'CH-001', 'B100' UNION ALL
SELECT 'CH-001', 'C100'

DECLARE @Table2 TABLE (Parent VARCHAR(4), UniqueNo INT, [Order] INT, ItemCode VARCHAR(2))

INSERT @Table2
SELECT 'A100', 1, 1, 'AK' UNION ALL
SELECT 'A100', 2, 2, 'AL' UNION ALL
SELECT 'A100', 3, 3, 'AM' UNION ALL
SELECT 'A100', 4, 4, 'OO' UNION ALL
SELECT 'AK', 5, 1, 'AM' UNION ALL
SELECT 'AK', 6, 2, 'OO' UNION ALL
SELECT 'AK', 7, 3, 'UK' UNION ALL
SELECT 'B100', 8, 1, 'BS' UNION ALL
SELECT 'B100', 9, 2, 'BT' UNION ALL
SELECT 'B100', 10, 3, 'AL' UNION ALL
SELECT 'B100', 11, 4, 'OO' UNION ALL
SELECT 'C100', 12, 1, 'OO' UNION ALL
SELECT 'C100', 13, 2, 'KO' UNION ALL
SELECT 'D100', 14, 1, 'DP' UNION ALL
SELECT 'D100', 15, 2, 'OO'

-- Show the expected output
SELECT t1.ProductNo,
t1.Component,
t2.UniqueNo,
t2.[Order],
t2.ItemCode
FROM @Table1 AS t1
LEFT JOIN @Table2 AS t2 ON t2.Parent = t1.Component
WHERE t2.ItemCode = 'OO'

UNION ALL

SELECT t1.ProductNo,
t2.ItemCode AS Component,
t3.UniqueNo,
t3.[Order],
t3.ItemCode
FROM @Table1 AS t1
LEFT JOIN @Table2 AS t2 ON t2.Parent = t1.Component
LEFT JOIN @Table2 AS t3 ON t3.Parent = t2.ItemCode
WHERE t3.ItemCode = 'OO'

UNION ALL

SELECT t1.ProductNo,
t3.ItemCode AS Component,
t4.UniqueNo,
t4.[Order],
t4.ItemCode
FROM @Table1 AS t1
LEFT JOIN @Table2 AS t2 ON t2.Parent = t1.Component
LEFT JOIN @Table2 AS t3 ON t3.Parent = t2.ItemCode
LEFT JOIN @Table2 AS t4 ON t4.Parent = t3.ItemCode
WHERE t4.ItemCode = 'OO'

UNION ALL

SELECT t1.ProductNo,
t4.ItemCode AS Component,
t5.UniqueNo,
t5.[Order],
t5.ItemCode
FROM @Table1 AS t1
LEFT JOIN @Table2 AS t2 ON t2.Parent = t1.Component
LEFT JOIN @Table2 AS t3 ON t3.Parent = t2.ItemCode
LEFT JOIN @Table2 AS t4 ON t4.Parent = t3.ItemCode
LEFT JOIN @Table2 AS t5 ON t5.Parent = t4.ItemCode
WHERE t5.ItemCode = 'OO'

ORDER BY 1,
2,
3,
4[/code]

Peter Larsson
Helsingborg, Sweden

erwine
Yak Posting Veteran

69 Posts

Posted - 2007-04-03 : 23:16:10

Hi Peter,

1st of all, thanks for replying.

Am i right to say that your solution is only working for less than 4 levels?

cheers,
erwine

... sql is fun...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-03 : 23:30:29

quote:
I dont want to use cursor to loop.

Well, This is the trade-off.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-04 : 00:40:45

Just add a new

UNION ALL

SELECT		t1.ProductNo,
		t5.ItemCode AS Component,
		t6.UniqueNo,
		t6.[Order],
		t6.ItemCode
FROM		@Table1 AS t1
LEFT JOIN	@Table2 AS t2 ON t2.Parent = t1.Component
LEFT JOIN	@Table2 AS t3 ON t3.Parent = t2.ItemCode
LEFT JOIN	@Table2 AS t4 ON t4.Parent = t3.ItemCode
LEFT JOIN	@Table2 AS t5 ON t5.Parent = t4.ItemCode
LEFT JOIN	@Table2 AS t6 ON t6.Parent = t5.ItemCode
WHERE		t6.ItemCode = 'OO'

Do you see the pattern?

Peter Larsson
Helsingborg, Sweden

erwine
Yak Posting Veteran

69 Posts

Posted - 2007-04-04 : 02:59:46

Hi,

Yeah..perhaps i need to get the max. level, using 3rd party codes to generate the query based on the level and then execute it.

thanks guys.

cheers,
erwine

... sql is fun...

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-04-04 : 10:28:07

quote:
Originally posted by erwine

I dont want to use cursor to loop. Do you guys have any idea to write a join statement for this?

Why not?!?!?
When you place ridiculous and unnecessary restrictions on your requirements, you end up with ridiculous and unnecessary code such as this.

e4 d5 xd5 Nf6

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-04 : 11:24:40

You don't want a CURSOR to loop?
But is it ok to loop, without a CURSOR?

Peter Larsson
Helsingborg, Sweden

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-04-04 : 13:32:18

Cursors are to be avoided, but not outlawed.
Though in this case a cursor is not necessary because a simple WHILE loop is both sufficient and efficient.

e4 d5 xd5 Nf6

erwine
Yak Posting Veteran

69 Posts

Posted - 2007-04-08 : 22:39:04

Hi guys,

Thanks for your opinions.

--------------------------------------------------------------------
Though in this case a cursor is not necessary because a simple WHILE loop is both sufficient and efficient.
--------------------------------------------------------------------

Admittedly, im not a gud sql programmer. However, im still not convinced that a simple WHILE will do. Or perhaps your 'simple' is my 'complicated'. Mind to share some knowledge of how to use WHILE loop to produce efficient result?

Thanks again for your kind attention.

cheers,
erwine

... sql is fun...

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-04-09 : 10:40:55

http://sqlblindman.googlepages.com/returningchildrecords

e4 d5 xd5 Nf6

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-04-11 : 09:10:10

Also refer

http://www.nigelrivett.net/RetrieveTreeHierarchy.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources