Mattias,
It may not be such a good idea to store the 6 items in 1 record. An
alternative structure is one table with Item ID and Name and another
with Item Id and Component Id, both of which point back to item id in
the first table. This avoids having to know how many components there
are and allows you to add a seventh component without changing the data
structure. See Jim Smith's excellent answer to a previous, similar
question (I've added it below).
If you want to fix what you've got then an OUTER join will help (see
docs) but sure as eggs is eggs someone will add a seventh component and
then you will have to fix every table and query and program, so if
you're still in design phase - then IMHO it's time to rethink the
structure.
Best regards,
Andy
<jim smith>
This is a classic problem known as a Bill of Materials explosion and
unfortunately relational databases don't handle it very well.
Storage is easy(ish).
Fundamentally you have a recursive many to many relationship between
components, resolved as
Component: Component_Link
id <-----------|---assembly_id
name |---subcomponent_id
That is 2 foreign keys back to the same master table, if the diagram
isn't
clear.
In OO terms, both item and kit are subclasses of component. There are
may
ways to implement that
in a relation database, but the simplest is to store them as a single
table
with a type field.
Retrieval is harder.
To get the contents of an assembly (kit),
select *
from component as assembly, component as subcomponent, component_link
where assembly.name=?
and component_link.assembly_id=assembly_id
and subcomponent.id= componentLink.subcomponent_id
BUT, this only goes down to one level which may be enough for most
purposes,
but for stock monitoring (e.g. I've sold 10 of kit ZZA102, what effect
does
that have
on my item stocks), you need to do it recursively.
With mysql ( and most other DBMS) the only alternative is to do the
recursion in a program -
ie
get all first level children
foreach get next level
foreach get next level
etc
Oracle has an excellent CONNECT BY extension to standard sql which does
this
brilliantly, and I believe mysql AB are
planning to imlpement it sometime.
</jim smith>
Mattias Larsson wrote:
>
> Hi there.
>
> I have a little problem with a sql-query I'm trying to get together.
> Well I got it to work halfways.
>
> I have a table with items, which are composed of 1-6 other items, which
> is saved in the same table.
>
> You might call it a recursive query, I need to get the names of the
> components that makes up the item I search for.
>
> It looks about like this: (3 CompX removed for readability)
>
> ID Name Comp1 Comp2 Comp3
> 1 Item1 0 0 0
> 2 Item2 0 0 0
> 3 Item3 0 0 0
> 4 Item4 0 0 0
> 5 Item5 1 3 4
>
> Item5 is a composition of item 1,3 and 4. It would be no problem if I only
> wanted to get the ID of the
> Included components but I want the names.
>
> I tried using:
> "SELECT i.Name, c1.Name, c2.Name, c3.Name
> FROM Items AS i, Items AS c1, Items AS c2, Items AS c3
> WHERE i.ID=5 AND i.Comp1=c1.ID AND i.Comp2=c2.ID AND i.Comp3=c3.ID;"
>
> This gives me the right result, but if an Item only consists of 2 components
> then I don't get anything
> Which is understandable. And I can only imagine what kinds of resources it
> will take if the Items table
> Gets really large.
>
> Is there a better way to do this? I want to keep the number of queries to a
> minimum. I have read some on
> Union, joins and subqueries but I can't think of a way to make it work.
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
