robots_stack_overflow, MySQL

Language: Layout:

#MySQL 5.7.12
#please drop objects you've created at the end of the script 
#or check for their existance before creating
#'\\' is a delimiter

###
#
#    Scroll down towards the bottom to see the query and results.
#
###

#Clean slate just in case
DROP TABLE IF EXISTS robot_targets;
DROP TABLE IF EXISTS person_targets;
DROP TABLE IF EXISTS people;
DROP TABLE IF EXISTS robots;
DROP TABLE IF EXISTS robot_partners;

CREATE TABLE robot_targets
    (`robot_target_id` int, `Robot_1` int, `Robot_2` int)
;
    
INSERT INTO robot_targets
    (`robot_target_id`, `Robot_1`, `Robot_2`)
VALUES
    (1, 1, 3),
    (2, 1, 4),
    (3, 1, 5),
    (4, 1, 6),
    (5, 1, 7),
    (6, 1, 8),
    (7, 1, 9),
    (8, 1, 10),
    (9, 1, 12),
    (10, 2, 3),
    (11, 2, 4),
    (12, 2, 5),
    (13, 2, 6),
    (14, 2, 7),
    (15, 2, 8),
    (16, 11, 12)
;

CREATE TABLE person_targets
    (`person_target_id` int, `person_id` int, `robot_id` int)
;
    
INSERT INTO person_targets
    (`person_target_id`, `person_id`, `robot_id`)
VALUES
    (1, 1, 3),
    (2, 1, 4),
    (3, 1, 5),
    (4, 1, 6),
    (5, 1, 7),
    (6, 1, 8),
    (7, 1, 9),
    (8, 1, 12),
    (9, 2, 3),
    (10, 3, 4),
    (11, 4, 5),
    (12, 5, 12),
    (13, 3, 5)
;

CREATE TABLE people
    (`person_id` int, `name` varchar(6), `robot_id` int)
;
    
INSERT INTO people
    (`person_id`, `name`, `robot_id`)
VALUES
    (1, 'Kris', 1),
    (2, 'Mike', 2),
    (3, 'Sue', 2),
    (4, 'Dora', 2),
    (5, 'Walter', 11)
;

CREATE TABLE robots
    (`robot_id` int, `name` varchar(14))
;
    
INSERT INTO robots
    (`robot_id`, `name`)
VALUES
    (1, 'Wall-E'),
    (2, 'Bender'),
    (3, 'R2D2'),
    (4, 'Data'),
    (5, 'HAL 9000'),
    (6, 'GlaDOS'),
    (7, 'ASIMO'),
    (8, 'The Terminator'),
    (9, 'Rosie'),
    (10, 'Optimus Prime'),
    (11, 'The Iron Giant'),
    (12, 'Clank')
;

CREATE TABLE robot_partners
    (`robot_partner_id` int, `Robot_1` int, `Robot_2` int)
;
    
INSERT INTO robot_partners
    (`robot_partner_id`, `Robot_1`, `Robot_2`)
VALUES
    (1, 1, 2),
    (2, 2, 1),
    (3, 1, 11),
    (4, 11, 1)
;

#Just to show you whats in the Tables
SELECT * FROM people;
SELECT * FROM robots;
SELECT * FROM robot_partners;
SELECT * FROM person_targets;
SELECT * FROM robot_targets;

#My query as it stands right now,  This one removes the duplicates but still has the extra 0 line when it isnt needed.
SELECT DISTINCT pt.person_id AS person_1, rt.robot_1 AS robot_1, rt.robot_2 AS target, rp.robot_2 AS robot_2, IFNULL(pt2.person_id, 0) AS person_2
FROM robot_partners AS rp
JOIN robot_targets AS rt ON (rp.robot_1 = rt.robot_1)
LEFT JOIN robot_targets AS rt2 ON (rt.robot_2 = rt2.robot_2 And rp.robot_2 = rt2.robot_1)
JOIN people AS p ON (p.robot_id = rt.robot_1)
JOIN person_targets AS pt ON (p.person_id = pt.person_id And pt.robot_id = rt.robot_2)
LEFT JOIN people AS p2 ON (rt2.robot_1 = p2.robot_id)
LEFT JOIN person_targets AS pt2 ON (p2.person_id = pt2.person_id And rt2.robot_2 = pt2.robot_id)
ORDER BY pt.person_id, rt.robot_1, rt.robot_2, rp.robot_2;

#Same as above but without DISTINCT and just returning NULL instead of converting it to 0.
SELECT pt.person_id AS person_1, rt.robot_1 AS robot_1, rt.robot_2 AS target, rp.robot_2 AS robot_2, pt2.person_id AS person_2
FROM robot_partners AS rp
JOIN robot_targets AS rt ON (rp.robot_1 = rt.robot_1)
LEFT JOIN robot_targets AS rt2 ON (rt.robot_2 = rt2.robot_2 And rp.robot_2 = rt2.robot_1)
JOIN people AS p ON (p.robot_id = rt.robot_1)
JOIN person_targets AS pt ON (p.person_id = pt.person_id And pt.robot_id = rt.robot_2)
LEFT JOIN people AS p2 ON (rt2.robot_1 = p2.robot_id)
LEFT JOIN person_targets AS pt2 ON (p2.person_id = pt2.person_id And rt2.robot_2 = pt2.robot_id)
ORDER BY pt.person_id, rt.robot_1, rt.robot_2, rp.robot_2;

#Cleaning up after myself.
DROP TABLE IF EXISTS robot_targets;
DROP TABLE IF EXISTS person_targets;
DROP TABLE IF EXISTS people;
DROP TABLE IF EXISTS robots;
DROP TABLE IF EXISTS robot_partners;


absolute service time: 0,45 sec