Hello everybody
Here is my problem
I have two tables.
First is Products and second is ProductsPart.
Products:
CREATE TABLE [Products]
(
[ID] [char] (16),
[NAME] [char] (40)
)
ProductsPart:
CREATE TABLE [ProductsPart]
(
[ID] [char] (16),
[IDPOD] [char] (16),
[QUANTITY] [smallint]
)
All products are in the products table.
Some of products are made of the other products which are also in the products table. From which products the product is made you know with ProductsPart table because IDPOD field shows product from which is made product which ID is ID.

Let's say that Product1 is made from Product2 and Product3
If I want to see from which product Product1 is made I will write this query:

SELECT P.ID, P.NAME
FROM Products P JOIN ProductsPart PP
ON P.ID = PP.IDPOD
WHERE PP.ID = 'Product1'

I will get this results:
ID2 Product2
ID3 Product3


But what if the Product3 is made of the others products(Product4, Product5) and so on...(Product4 = Product6 + Product7)...
How can I make query get this results?
ID2 Product2
ID3 Product3
ID4 Product4
ID6 Product6
ID7 Product7
ID5 Product5

Thanks very much
Alex

Select * from Products where iD in
(Select ID from Products where ID in (
Select DPOD
From ProductsPart
Where ID = 8
)
union
Select ID from Products
where ID in(
select DPOD from ProductsPart
where ID
in
(Select DPOD
From ProductsPart P
Where P.ID = 8)
))

This will solve your Problem


Deepesh Jain
VB,VBA & .NET Specialist
Wiley Support Team

There really isn't a good way to process a tree like this, assuming an arbitrary depth, without using some form of recursion, and SQL doesn't "do" recursion.

If your tree has a known fixed depth limit, you can construct a query using outer joins or subqueries to "flatten" out the hierarchy.

There are other structures which will allow you to define and traverse a tree of arbitrary complexity, but that subject is quite complex and really beyond the scope of any discussion here. I suggest you obtain the book "SQL for Smarties" by Joe Celko - he devotes an entire chapter to the problem of defining and processing trees in SQL - in fact I understand he is soon coming out with an entire book on the subject.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com