Trixmoto's response (same as your last post's example) is the only way to go in a relational database.
MM table examples for these "relationship tables" are
ArtistSongs,
ArtistsAlbums, ... (every time references between records in 2 different tables, but those could also be references to records within 1 single table)
For 1 - 0, 1 - 1 or 1 - multiple relations (which include parent-child relations which can be looped recursively) you only need 2 tables, or in your case 1 single table where the records are refering to other records in the same table.
If you want multi - multi relations, the only way to do that is with adding an extra table that defines each relationship with ID1 and ID2.
Table1:
ID, Desc, ...
Table2:
ID, Desc, ...
Ref: ID,
ID1,
ID2, ... (with ID1 refering to Table1.ID and ID2 refering to Table2.ID)
E.g. this would show all relations between Table1 and Table2 (supposing the syntax is correct):
Code: Select all
SELECT
T1.Desc,
T2.Desc
FROM
Ref AS R
INNER JOIN Table1 AS T1
ON R.ID1 = T1.ID
INNER JOIN Table2 AS T2
ON R.ID2 = T2.ID
WHERE
T1.Name = 'Test'
If you change (only)
Table2 to
Table1, then you have records from Table 1 refering to other records in Table1, which is what you want.
In a badly designed database (or if it's the only alternative), you could also work like
Code: Select all
ID | Desc | Refs
0 | item1 | 1,2,3,4,5,6,7,8,9
1 | item2 | 0,2,3,4,
2 | item3 | 5,6,7,8,9
3 | item4 | 0,1,7,8,9
...
but the values in the Refs column then would have to be parsed in your program, outside the database engine, which would make it quite slow.