1

I am curious what you think is the best way to get this MySQL database to work.

i had this table:

family.id
family.name
family.related

And thought it wouldnt be a problem to connect related family members together in 1 query like this:

id        | name        | related
1         | Name1       | 
2         | Name2       | 3,5
3         | Name3       | 
4         | Name4       | 
5         | Name5       | 4,1
6         | Name6       | 
7         | Name7       | 
8         | Name8       | 6
9         | Name9       | 7

So Name2 is related to Name3 and Name5, where Name5 have other related id's, so i should make a query to get the following id's:

Selecting id 2, outputs related id's: 3,5,4,1

Selecting id 6, outputs related id's: 8

Selecting id 9, outputs related id's: 7

But i cant find a query to get all the related id's in one column.

Is it even possible?

WHOMEZz
  • 79
  • 7
  • Oracle has hierarchical query functions to do that. I wonder if there is a similar construct for mysql? – OldProgrammer Apr 09 '13 at 19:35
  • 1
    You want to represent a hierarchy in SQL table. There is a good answer, describing, how to it efficiently: http://stackoverflow.com/questions/192220/what-is-the-most-efficient-elegant-way-to-parse-a-flat-table-into-a-tree – user4035 Apr 09 '13 at 19:38
  • I am sorry, but i cannot make sense out of the answer, can you please help explaining how to use it for the databse structure above? – WHOMEZz Apr 09 '13 at 19:59

1 Answers1

0

For a many-to-many relationship, you'll generally need to use a linking table:

family_id
related_to

Where, when you would have several comma-delimited items in your family table, you would instead have one entry in the linking table for each item that would be in the comma-delimited list:

family_id | related_to
2           3
2           5
5           4
5           1
...

And so on.

Adrian
  • 42,911
  • 6
  • 107
  • 99
  • but when i select family_id 5, how do i get family_id 2 outputted as well? How will the query look like, can i do it in the same query when looking up information in the family table? with a JOIN for example? – WHOMEZz Apr 09 '13 at 20:08
  • Is there anyone that can help me out with this problem? – WHOMEZz Apr 10 '13 at 12:09
  • You can `SELECT * WHERE family_id = X OR related_to = X`. – Adrian Apr 10 '13 at 22:15