MySQL 8.0

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


version()


8.0.22


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"), (1, NULL, "Team A");

-- NOTE - new record added with NULL for sec_contact_id
SELECT * FROM name_lookup;
SELECT * FROM contact_info ORDER BY pri_contact_id, id;


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

3
1
null
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
ORDER BY pri_contact_id, - ci1.id DESC;


id
contact_id
contact_name
pri_contact_id
sec_contact_id
blah_blah


1
1
John Doe
1
3
Team A

1
1
John Doe
1
null
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


--
-- NOTE that this is the result for the question, AS ASKED, but the 
-- data for the record with sec_contact_id is no longer present!
--

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


--
-- Result WITH sec_contact_id = NULL
--

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


Con_1 name
Con_2 name
Team


John Doe
Jose Garcia
Team A

John Doe
null
Team A

Larry Brown
Mary Smith
Team B


--
-- Performance for both solutions is analyzed below (MySQL >= 8.0.16)
-- They are identical, so adding this shouldn't impact performance adversely
-- 
EXPLAIN ANALYZE
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;


EXPLAIN


-> Sort: nl1.id  (actual time=0.078..0.078 rows=2 loops=1)
    -> Stream results  (cost=3.45 rows=3) (actual time=0.058..0.063 rows=2 loops=1)
        -> Inner hash join (nl2.contact_id = ci1.sec_contact_id)  (cost=3.45 rows=3) (actual time=0.056..0.059 rows=2 loops=1)
            -> Table scan on nl2  (cost=0.12 rows=4) (actual time=0.005..0.008 rows=4 loops=1)
            -> Hash
                -> Inner hash join (nl1.contact_id = ci1.pri_contact_id)  (cost=2.00 rows=3) (actual time=0.036..0.040 rows=3 loops=1)
                    -> Table scan on nl1  (cost=0.12 rows=4) (actual time=0.006..0.007 rows=4 loops=1)
                    -> Hash
                        -> Table scan on ci1  (cost=0.55 rows=3) (actual time=0.015..0.018 rows=3 loops=1)



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


EXPLAIN


-> Sort: nl1.id, ci1.blah_blah  (actual time=0.072..0.072 rows=3 loops=1)
    -> Stream results  (cost=5.11 rows=48) (actual time=0.053..0.060 rows=3 loops=1)
        -> Left hash join (nl2.contact_id = ci1.sec_contact_id)  (cost=5.11 rows=48) (actual time=0.050..0.056 rows=3 loops=1)
            -> Left hash join (nl1.contact_id = ci1.pri_contact_id)  (cost=1.67 rows=12) (actual time=0.021..0.025 rows=3 loops=1)
                -> Table scan on ci1  (cost=0.55 rows=3) (actual time=0.005..0.007 rows=3 loops=1)
                -> Hash
                    -> Table scan on nl1  (cost=0.22 rows=4) (actual time=0.005..0.007 rows=4 loops=1)
            -> Hash
                -> Table scan on nl2  (cost=0.05 rows=4) (actual time=0.015..0.018 rows=4 loops=1)
id	contact_id	contact_name	pri_contact_id	sec_contact_id	blah_blah
1	1	John Doe	1	3	Team A
1	1	John Doe	1	null	Team A
4	4	Larry Brown	4	2	Team B
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