MySQL 5.6

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version()


5.6.50


CREATE TABLE name_lookup
(
  id INT UNSIGNED AUTO_INCREMENT,
  contact_id INT,
  contact_name VARCHAR(32),

  PRIMARY KEY(id)  
);  
CREATE TABLE contact_info
(
  id INT UNSIGNED AUTO_INCREMENT,
  pri_contact_id INT,
  sec_contact_id INT,
  blah_blah VARCHAR(32),

  PRIMARY KEY(id)  
);  
INSERT INTO name_lookup(contact_id, contact_name) 
VALUES
(1, "John Doe"), (2, "Mary Smith"), 
(3, "Jose Garcia"), (4, "Larry Brown");
INSERT INTO contact_info(pri_contact_id, sec_contact_id, blah_blah) 
VALUES(1, 3, "Team A"), (4, 2, "Team B");
SELECT * FROM name_lookup;
SELECT * FROM contact_info;


id
contact_id
contact_name


1
1
John Doe

2
2
Mary Smith

3
3
Jose Garcia

4
4
Larry Brown




id
pri_contact_id
sec_contact_id
blah_blah


1
1
3
Team A

2
4
2
Team B


SELECT 
  nl1.id, nl1.contact_id, nl1.contact_name, 
  ci1.pri_contact_id, ci1.sec_contact_id, ci1.blah_blah
FROM name_lookup nl1
JOIN contact_info ci1 
  ON nl1.contact_id = ci1.pri_contact_id;


id
contact_id
contact_name
pri_contact_id
sec_contact_id
blah_blah


1
1
John Doe
1
3
Team A

4
4
Larry Brown
4
2
Team B


SELECT 
  nl1.id, nl1.contact_id, nl1.contact_name,
  nl2.id, nl2.contact_id, nl2.contact_name,
  ci1.pri_contact_id, ci1.sec_contact_id, ci1.blah_blah
FROM name_lookup nl1
JOIN contact_info ci1 
  ON nl1.contact_id = ci1.pri_contact_id
JOIN name_lookup nl2
  ON ci1.sec_contact_id = nl2.contact_id
ORDER BY nl1.id;


id
contact_id
contact_name
id
contact_id
contact_name
pri_contact_id
sec_contact_id
blah_blah


1
1
John Doe
3
3
Jose Garcia
1
3
Team A

4
4
Larry Brown
2
2
Mary Smith
4
2
Team B


SELECT 
  nl1.id, nl1.contact_id AS "1st contact", nl1.contact_name AS "Con_1 name",
  nl2.id, nl2.contact_id AS "2nd contact", nl2.contact_name AS "Con_2 name",
  ci1.pri_contact_id, ci1.sec_contact_id, ci1.blah_blah AS "Team"
FROM name_lookup nl1
JOIN contact_info ci1 
  ON nl1.contact_id = ci1.pri_contact_id
JOIN name_lookup nl2
  ON ci1.sec_contact_id = nl2.contact_id
ORDER BY nl1.id;


id
1st contact
Con_1 name
id
2nd contact
Con_2 name
pri_contact_id
sec_contact_id
Team


1
1
John Doe
3
3
Jose Garcia
1
3
Team A

4
4
Larry Brown
2
2
Mary Smith
4
2
Team B


SELECT 
  nl1.contact_name AS "Con_1 name",
  nl2.contact_name AS "Con_2 name",
  ci1.blah_blah AS "Team"
FROM name_lookup nl1
JOIN contact_info ci1 
  ON nl1.contact_id = ci1.pri_contact_id
JOIN name_lookup nl2
  ON ci1.sec_contact_id = nl2.contact_id
ORDER BY nl1.id;


Con_1 name
Con_2 name
Team


John Doe
Jose Garcia
Team A

Larry Brown
Mary Smith
Team B


+-------------+-------------+--------+
| John Doe    | Jose Garcia | Team A |
+-------------+-------------+--------+
| Larry Brown | Mary Smith  | Team B |
+-------------+-------------+--------+
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '+-------------+-------------+--------+
| John Doe    | Jose Garcia | Team A |
+-' at line 1
id	contact_id	contact_name	id	contact_id	contact_name	pri_contact_id	sec_contact_id	blah_blah
1	1	John Doe	3	3	Jose Garcia	1	3	Team A
4	4	Larry Brown	2	2	Mary Smith	4	2	Team B