Two joins on same table in SQL
You can assign a table alias when you join tables, just like you do with the column names. Not only does it save you a lot of typing, but it makes the code a little more readable, and it solves the problem of joining the same table twice.

SELECT p1.name AS "primary",
       relationships.related_person_id AS relatedto,
       p2.name AS related_name
FROM people AS p1
INNER JOIN relationships AS r
    ON p1.name_id=r.primary_person_id 
INNER JOIN people AS p2
    ON p2.name_id=r.related_person_id 
ORDER BY p1.name_id;


In the example above, I've assigned p1 and p2 to the people table and r to the relationships table.

Note: some database platforms don't like the "AS" keyword. You can skip "AS" alltogether if you want, like so:

...
FROM people p1
INNER JOIN relationships r
...