--	test with: mysql < multijoin.sql

USE test;

DROP TABLE IF EXISTS person;
CREATE TABLE person (
	id	INTEGER
	,name	CHAR(24)
);

INSERT INTO person
	(id, name)
VALUES
	(1, "pizza")
	,(2 , "yoko")
	,(3, "sally")
;

DROP TABLE IF EXISTS threeway;
CREATE TABLE threeway (
	id		INTEGER
	,person1	INTEGER
	,person2	INTEGER
	,person3	INTEGER
	,PRIMARY KEY (id)
);

INSERT INTO threeway
	(id, person1, person2, person3)
VALUES
	(1, 1, 2, 3)
	,(2, 3, 2, 1)
	,(3, 1, 3, 2)
;

-- pull all the records out of threeway, translating the names to the
-- entries in person

SELECT
	p1.name		AS person1
	,p2.name	AS person2
	,p3.name	AS person3
FROM
	threeway
JOIN
	person p1
ON
	threeway.person1 = p1.id
JOIN
	person p2
ON
	threeway.person2 = p2.id
JOIN
	person p3
ON
	threeway.person3 = p3.id
WHERE
	threeway.id IN (1, 2, 3);