Brainstorming: Possible multi-select query to single query?

Brief description of tables:

part table: id, nameid and ids of parts. if there are multiple sub-parts for a part, then the name appears multiple times, each time with an id of the sub-part. So it's easy to find the complete list of sub-parts for a part.

eg:
a,b,c
1,1,1
2,2,2
3,1,3
4,2,3

where 'a' is the auto-increment field for the part table
'b' is the part id
'c' is the name id.

So when a part is entered (eg. 'part1'), a list of sub-parts that make up that part is presented (assume 'part1' = 3, so 1 & 2 are presented, showing that subpart1 and subpart2 make up that part), then user can click on each sub-part to get all the information.

Now management want multiple part numbers which are exactly the same set of sub-parts. This is easy, we can extract sub-parts from a previous part which is setup and apply them to the new part:

5,1,4
6,2,4

However, in the future if another sub-part is added to a part, it will not be automatically added to the other parts which are identical is all but name.

Since these additional parts are basically just aliases of existing parts, we can create another table (lets call it aliases) which matches a name id to a part id (so we lookup partid4 directly), or one name to another name (nameid4=nameid3, then lookup nameid3 in the part table). partid lookups will be quicker as the auto incremented column is indexed and unique, and I can see some time in the future, management wanting a part to be the contents of another part plus some sub-parts.

So, lets ignore names for now, if we change the first type like so:
a,b,c
1,1,1 [sub part 1]
2,2,2 [sub part 2]
3,1,3 [part id 3 contains sub part 1]
4,2,3 [and also sub part 2]
5,5,4 [sub part 3]
6,3,5 [part id 6 contains part id 3]
7,5,5 [plus sub part 3]

That looks nasty doesn't it? Referencing an id that just points to other, and doesn't even include all of them.

I know I can solve this problem with multiple select queries and loops in php, but I really want as much as possible in the sql side and less eating cpu cycles parsing php code.

Any ideas from other people? I'm thinking there must be an easier way.

I can make slight changes to the database and structure, along with code alterations, but don't really want to rewrite the entire system to use it.

One last thing: The server is MySQL 4.1.

Yeah, the classical "tree" problem.

And I think you goofed in your "tree" presentation. If "a" is an autonumber field, how'd you get duplicate values???

Let's rewrite that a bit more meaningfully. Let's use the partname in place of the nameid, for clarity. And if a part doesn't contain any other parts, try using NULL instead of duplicateing the autoid field:

autoid :: contains :: partname
1 :: null :: subpartA
2 :: null :: subpartB
3 :: 1 :: partX [partX contains subpartA]
4 :: 2 :: partX [partX contains subpartB]
5 :: null :: subpartC
6 :: 3 :: partY [partY contains subpartB]
7 :: 5 :: partY [partY contains subpartC]

Do I have that right??

Now a question: With this system, I don't see any way to have sub-sub parts. Is that okay? Is that intended??

There *are* other ways to organize the data so that you can have multi-level trees, but I don't see a way to do that with this system.

Is it possible that you were trying to say that your column "a" is a *FOREIGN* key to another table with part number/names???

Hmmm...but then what's the point of your column "c"???

I'm a bit lost.

Yeah, the classical "tree" problem.
And I think you goofed in your "tree" presentation. If "a" is an autonumber field, how'd you get duplicate values???
Yes, I goofed.
if a part doesn't contain any other parts, try using NULL instead of duplicateing the autoid field:
Good idea. That would make more sense.
Do I have that right??Yes, perfect.
Now a question: With this system, I don't see any way to have sub-sub parts. Is that okay? Is that intended??At the moment, yes. However, management already want aliases, so they can have parts where the only difference is the name of the part. I can see not too long in the future for them to want sub-sub parts. I don't see it going any further than that however (sub-sub-sub is likely never happen).
There *are* other ways to organize the data so that you can have multi-level trees, but I don't see a way to do that with this system.Neither do I, so I appreciate your comments on how you would go about doing such a tree system.
Is it possible that you were trying to say that your column "a" is a *FOREIGN* key to another table with part number/names???No, column A is simple auto-number, I just goofed when I typed the table up above. Column C is indeed the id of the name.

Okay, for starters, I'd just ditch the autonumber column.

It isn't doing anything useful and I can't see any query that would ever depend on it.

And then I'd just have the table look like:


partID : subPartID

Where *both* those id's refer into the "part names" table.

Again, for clarity, I'm going to use names in my example table instead of numbers.

So:

TABLE NAME: assemblies
partID : subpartID
partX : partA
partX : partB
partY : partB
partY : partC
partXXX : partD
partXXX : partY


So that means the partXXX is really

partXXX
partD
partY
partB
partC

That is, we now handle sub-sub parts.

And now parts that are *ONLY* used as sub-parts are *NOT* shown in this table.

If they need to kept in another table (for what??), fine. But they don't appear in this table.

To find all the sub-parts *AND* sub-sub-parts that make up one assembly, your query looks like this:

SELECT 1 as ordering, partID, subPartID FROM assemblies
UNION
SELECT 2, a1.partID, a2.subPartID
FROM assemblies AS a1, assemblies AS a2
WHERE a1.subpartID = a2.partID
ORDER BY partID, ordering, subpartID

For the above table (and again using names instead of ids) that would give us:

1 :: partX :: partA
1 :: partX :: partB
1 :: partY :: partB
1 :: partY :: partC
1 :: partXXX :: partD
1 :: partXXX :: partY
2 :: partXXX :: partB
2 :: partXXX :: partC

And, yes, it could easily be extended to sub-sub-sub parts. Past about 4 levels, though, I'd go with a different scheme.