On Sun, 8 Apr 2001, Realbad Atgames wrote:
>
> --- [EMAIL PROTECTED] wrote:
> >
> > > Hello, I'm new to MySQL and have ran into a
> > problem in
> > > designing my database. I have a table of items, A,
> > and
> > > each item should have any number of other records
> > from
> > > another table, B, associated with it. I also need
> > to
> > > have any number of records from table C associated
> > > with table B. They need to be associated in such a
> > way
> > > that I can quickly find out what is associated
> > with a
> > > record in table A, as well as what is associated
> > with
> > > a record in table B. In other words, how do I
> > store an
> > > array of pointers? What's the best way to do this?
> > I
> > > couldn't find anything relevant in the docs.
> >
> > I'm not sure that I've understood correctly, but
> > perhaps :
> >
> > create table A (
> > aid int unsigned not null auto_increment,
> > value char(1000),
> > key idxaid (aid)
> > );
> > create table B (
> > bid int unsigned not null auto_increment,
> > aid int unsigned not null default -1,
> > value char(1000),
> > key idxbid (bid),
> > key idxaid (aid)
> > );
> > create table C (
> > cid int unsigned not null auto_increment,
> > bid int unsigned not null default -1,
> > value char(1000),
> > key idxcid (cid),
> > key idxbid (bid)
> > );
> >
> > That way, reading data from Table B tells you about
> > the parent,
> > likewise for C.
> Thanks for you reply..but I want to be able to find
> the list of the children, not parents. For example:
>
> In table A: foo quux (separate from B's quux)
> / \ /
> In table B: baz quux bar
> /|\ \
> In table C: w x y z
>
> Each table's rows (except for table C) should be
> allowed to have any number of children, and I should
> be able to get a list of all the children of a parent.
>
>
> In C, I would do it like this:
>
> typedef struct _A
> {
> int value;
> B* children[100];
> } A;
>
> typedef struct _B
> {
> int value;
> C* children[100];
> }
>
> typedef struct _C
> {
> int value;
> }
>
> How would I do this in MySQL?
>
The question is not a Mysql issue but rather simple sense and logic.
Make your tables as:
A: aname char()
aid int auto_increment
B: bname char()
bid int auto_increment
aid int which you'll set depending on which record of A this specific record
of B is a child of
C: cname char()
bid int which you'll set depending on which record of B this specific record
of C is a child of
To find out all the B_children of record 'A123' from table A you'd do:
select bname from A,B where A.aid=B.aid AND A.aname="A123";
Same for finding the C_children of a record from table B.
Hell! you can even get the grandchildren in table C that belong to a record 'A123'
from table A:
select cname from A,B,C where A.aid=B.aid AND B.bid=C.bid AND A.aname="A123";
regards,
thalis
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
