Bhavbhuti --

1. I think you have what's called a "parts explosion" logical data model. You have an assembly, which is made up of subassemblies, which may, in turn, be made up of subassemblies. Is that correct?

(A note. SQL wasn't really designed to address hierarchical queries like this, at least in commercially available forms. To accomplish that, you need to do a query for each level and have to know how many levels max you're dealing with. I think the SQL 3 standard allows for a generalized query format for hierarchical data.)

2. If I understand your table design, the way you've physically modelled that is in a highly denormalized fashion. The table actually represents a complete assembly, and each of the fields represents a sub assembly of that.

(That may be handy for data entry, but it works poorly for relational operations such as SQL SELECT.)

3. It's not clear what you want to get out of the data with your query. Do you want to get the total number of each type of part? In that case, you're flattening out the hierarchy. I'd just suggest creating a parts table, go through the data with regular Fox code, and populate the parts table, then do a report on it. SQL would, I think, take more code to accomplish this because your tables are denormalized.

HTH,

Jay



>FPD25 - Recursive SQL, data, tree and my SQL below
>
>I have the following Databases and its data
>
>
>***dbf - A09MITEM
>**desc **docid **uniqueid
>FinProd 1
>lvl1 itm1 2
>lvl1 itm2 3
>lvl1 itm3 4
>lvl2 itm1 5
>lvl2 itm2 6
>lvl2a itm1 7
>lvl2a itm2 8
>lvl3 itm1 9
>lvl3 itm2 10
>lvl3a itm1 11
>lvl3a itm2 12
>lvl4 itm1 13
>lvl4 itm2 14
>
>
>***dbf - A09SITEM
>**docid **uniqueid
>1 2
>1 3
>1 4
>etc 4
>etc 5
>etc 6
>
>typically (but not necessary and never predictable) the connections of the items come as below:
>
>FinProd
> |
> +--> lvl1 itm1
> | |
> | +--> lvl2 itm1
> | | |
> | | +--> lvl3 itm1
> | | |
> | | +--> lvl3 itm2
> | |
> | +--> lvl2 itm1
> |
> +--> lvl1 itm2
> | |
> | +--> lvl2 itm1
> | | |
> | | +--> lvl3 itm1
> | | |
> | | +--> lvl3 itm2
> | |
> | +--> lvl2a itm1
> | | |
> | | +--> lvl3a itm1
> | | | |
> | | | +--> lvl4 itm1
> | | |
> | | +--> lvl3a itm2
> | |
> | +--> lvl2 itm1
> |
> +--> lvl1 itm3
>
>
>SELECT mi.*, si.docid, si.uniqueid ;
> FROM a09sitem si, a09mitem mi ;
> WHERE si.docid = mi.uniqueid ;
>UNION ALL ;
>SELECT mi.*, mi.docid, mi.uniqueid ;
> FROM a09mitem mi ;
> INTO CURSOR temp1
>
>
>SELECT * ;
> FROM temp1 ;
> ORDER BY uniqueid_a, docid_b
>
>
>now this SQL goes only one level and I am lost
>
>Please Help.
>
>Thanks in Advance
>
>Regards
>Bhavbhuti