> This question I am posting for a friend.... Below is post to another list...
>
As always the answer is: "It depends on!" ;)
> How does one create a self-join in MySQL tables? I've only played a little
> with SQL selects so far, some inner joins, and now I have to create a
> many-to-many relationship between records in the same table. So, I have a
> links table with the local key and the foreign key, that will link record A
> to record B, but I'd like the same record to also link back from B to A..
> This is what I have now, 'table' is the related table, in this case the same
> as the current table, local_id the key value of the current record:
>
> "SELECT t.id, t.title FROM table AS t INNER JOIN links AS l ON t.id =
> l.foreign_id WHERE l.local_id = " & local_id;
>
> Do I just run this query twice, swapping the id_fields, and merge the result
> sets? Or do I have to use two records for each both-ways link? Or is there
> an ugly way that puts both ids in the same field and does a LIKE query? How
> is this usually solved?
By an additional table actin holding relation information.
> One reply came in, and is below, with more info
> >If I'm not mistaken you need to alias the the same table twice. I'm also not
> >sure whether MySQL's query optimizer can deal with it:
> >
> >
> >SELECT t1.id, t1.title , t2.name
> >FROM table AS t1, table AS t2
> >WHERE t2.link = t1.id
>
> This is a self-join one-to-many though, isn't it?
Yes and no, because the property of being one-to-may has not to
do with the query it self, rather the data stored in the table
(e.g. the intention with the table).
For example:
table A defines as: Id int, Person vrchar, Maried_to int
select * from A
Id Person married_to
1 Bob 3
2 John 4
3 Berlinda 1
4 Jane 2
select t1.person, t2.person
from a as t1, A as t2
where married_to = Id
while create a symmetric relation (eg if A is married to B then B is also married to A)
However, this is not a many to many relation but a 1-1 relation. Since one person is
married to one and only one person.
However imagine we have an nochristiona tradition,
then it might turn out to be like this:
Id Person married_to
1 Bob 3
2 John 4
3 Berlinda 1
4 Jane 2
5 Beth 1
And then we will have a one-to-many situation since
select t1.person, t2.person
from a as t1, A as t2
where married_to = Id
and t2.person = 'Bob'
will yield the result:
t1.person t2.person
Berlinda Bob
Beth Bob
But this is kind of a strange relation, since
Both Beth and Berlinda is married with Bob, but Bob
is claimed to only be married with Berlinda
so we introduce
1 Bob 5
and run the query:
select t1.person, t2.person
from a as t1, A as t2
where married_to = Id
and NOT
(
and NOT t1.Person = 'Bob'
and NOT t2.person = 'Bob'
)
and then suddenly we se that we have a many-to-may relation:
t1.person t2.person
Berlinda Bob
Beth Bob
Bob Berlinda
Bob Beth
Finaly:
It is possible to get rid of the symmetric properties but that is the
advance course we wont have today (e.g. I need to do some real job to.:)
> >BTW, I think self joins are usually a sign of poor schema design - kind of a
> >filemaker thingy. The only time I found it semi-useful was in building a
> >hierarchical concept in a SQL table.
>
> Here, the records are people, and each person is linked to any number of
> other persons via relationships such as father, grandmother, aunt, husband
> etc. Record A can have a daughter-link to record B, which has a father-link
Any many to many relation could preferable be model with a "relation table"
carrying primary keys and a kind of semantic key since you then avoid duplication
of records as in my example above.
The example above could be modeled as such:
Table A: Id Person
Table A-into-A: Id1 Type Id2
Table A:
Id Person Sex
1 Bob 1
2 John 1
3 Berlinda 0
4 Jane 0
5 Sam 1
Table A-into-A
1 Married 3
2 Married 4
3 Married 1
4 Married 2
2 Father 1
4 Father 1
1 Father 5
Father to could be searched as (not tested):
Select t1.person, t2.person
from a as t1, A-ito-A as t2
where t1.id = t2.id
and t2.id = t1.id
and Type = 'Father'
and t1.Sex = 1
and t2.Sex = 1
Then t1.person is father to t2.person, and t2.person is sun to t1.person
It is also possible to extract, for ex, only homosexual relation with
this kind of model, so one needs to consider what kinds of information
that one should store, and how to store it in order to prevent abuse
of the stored information. (Remember how SS/Gestapo abused the city
population archive of the Netherlands during the Second world war!)
DISCLAIMER:
I haven't tested any code above, so there is probably some errors
within it somewhere - if you have trouble let me know and I
will make an errata.
> back to record A. Both have many more links to other records, with some
> overlap (both can link to C, one as brother-relationship, one as
> son-relationship). One has to be able to add/remove any link(s). The whole
> picture doesn't need to be consistent, two records can link to eachother
> each using a grandfather-relationship, it will be up to the admin to
> avoid/fix this.
Grand-realtions sould be reqursive resolved in the query. (If you don't have any
particular speed concerns....)
---------------------------------------------------------------------
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
