Table with 2 FK pointing to the same table

advertisements

I use MySQL with MyISAM engine. Lets say I have the following 2 database tables:

TableX

ID, Person1ID, Person2ID

Person

ID, Name

How should my query look like since I have 2 foreign keys that points to the same table? I've been trying to join tables but out of luck...

I want to select

ID, Name, Name


You would need to join Person table twice in your case, here is an example:

SELECT j.ID,
       p.Name AS `Person 1 Name`,
       p2.Name AS `Person 2 Name`
  FROM TableX j
  JOIN Person p
    ON p.ID = j.Person1ID
  JOIN Person p2
    ON p2.ID = j.Person2ID