adhir@stripped wrote:
>
> Hey all - a question for the DB experts out there:
>
> Say I have a table of records with primary key id (an auto inc field).
>
> I need to be able to relate records within the table to one another as peers, so
> if entry id 4 is related to entry id 7, the converse should also be true.
>
> One way to do this is create a table 'relationships' with two fields, id1 and
> id2. When I want to establish a relationship between entries 4 and 7, I can
> say:
>
> insert into relationships set id1=4,id2=7
>
> Now if I want to get all entries that id 7 is related to, I need to
>
> select id1,id2 from relationships where id1=7 or id2=7
>
> and discard the return value containing id=7 - maybe by doing
>
> select if(id1=7,id2,id1) from relationships where id1=7 or id2=7
>
> This is sort of hokey though.
>
> So another thing to do is along with the insert statement above, always insert
> the converse, so I would have done:
>
> insert into relationships set id1=4,id2=7
> insert into relationships set id1=7,id2=4
>
> Now to get all entries that 7 is related to, I simply say:
>
> select id2 from relationships where id1=7
>
> Problem solved. But its not clean... Can anyone suggest a data structure which
> would do this "cleanly"?
>
> Thanks...
>
> Al
Hi Al
Why don't you use your first relationships table and issue two SELECTs on it to first get
the ones with id1=7 and then get with the secong SELECT the other ones?
Your table structure seems OK.
Tschau
Christian
