Link rows in a MySQL table

I have a question about MySQL. I have this table structure:

ID      Name
------  ------------
1       Peter
2       John
3       Carl
4       William

I need to link the record. I.e. - Peter is related to Carl and William - Carl is related to John

Should I make a new table like this:

ID1     ID2
------  ------------
1       3
1       4
3       2

Or should I extend the first table like this:

ID      Name          Links
------  ------------  ----------
1       Peter         3,4
2       John
3       Carl          2
4       W开发者_JAVA百科illiam

In both cases, how do I make a query that returns:

Name          LinkedName
------------  --------------
Peter         Carl
Peter         William

I have considered to use JOIN, UNION and sub-queries, but I can not really get it to work. I really hope that somebody can help me here.

Thanks.

As it is an n:m relationship ("Peter is related to Carl and William"), your first idea is the right one: an additional table with two IDs. You could add further information about the relationship there. Also add foreign keys and a primary key to prevent duplicate entries.

CREATE TABLE rel (
    from_id integer references person(id),
    to_id integer references person(id),
    primary key(from_id, to_id)
);

Query like this:

SELECT p1.name, p2.name AS linked_name
  FROM person p1
  JOIN rel r ON (r.from_id = p1.id)
  JOIN person p2 ON (r.to_id = p2.id)
 WHERE p1.name = 'Peter';

Add a new table as yiu suggested. Then you can select them by join the id table and then the main table again like:

Select t1.name, t3.name as linkedname from t1 left join table_ref t2 on t1.id = t2.id1 left join t1 as t3 on t3.id = t2.id2