CODEWITHSUNDEEP
phpmysql
FredTheLover
FredTheLover

Reputation: 1019

Tricky MySQL query

I have a table of siblings with three columns (KEY, personID, personID)

Here as an example:

0|1|2
1|2|3
2|1|4
3|4|5
4|1|6
5|5|7

Notice all the persons with IDs 1 to 5 are siblings.

Now what is the best way to remove one sibling while making sure(in all cases) all the other siblings stay siblings.

A tricky case is removing sibling with ID=4. If this is not possible with SQL alone then what would the best way be to do it with PHP + MySQL.

The code I came up with is very long and seemingly inefficient. If anyone has any other ideas I would love to hear.

Upvotes: 1

Views: 125

Answers (1)

Niet the Dark Absol
Niet the Dark Absol

Reputation: 324620

The problem with your current layout is that you basically have several nodes (people) and a few bonds between them. These bonds come with the implicit deduction that "if A with B and B with C, then A with C", and this just doesn't work for a relational database.

Ideally, you need all the bonds in both directions. Then you can safely delete people as needed.

Upvotes: 1

Related Questions