What's the proper way to alias my outer join in mysql so I don't get 'Not unique table/alias' errors?

Question

While attempting to grab both a player's name as well as their damage was done from a players table and a join table including reports and players(reports_players table), I'm receiving ERROR 1066(42000): Not unique table/alias: 'rp'

I had also wanted to include item_level into this query but wanted to get this part working first.

I've tried using this query to grab the two values I want:

SELECT p.name
FROM players p
LEFT JOIN rp ON p.player_id=rp.player_id
UNION
SELECT rp.damage_done
FROM reports_players rp
RIGHT JOIN rp ON p.player_id=rp.player_id
GROUP BY p.name;

which is directly referenced from How to do a FULL OUTER JOIN in MySQL? and I'm still unclear as to why it's wrong. I've used multiple of the examples of a full join and I'm still not able to get anything other than an empty set or a query that gets hung up forever.

This is my db structure:

CREATE TABLE reports(
report_id INT UNSIGNED auto_increment PRIMARY KEY,
zone INT UNSIGNED,
report_url VARCHAR(20),
title VARCHAR(50)
)engine=InnoDB CHARSET utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE reports_players(
id INT UNSIGNED auto_increment PRIMARY KEY,
report_id INT UNSIGNED UNIQUE KEY,
player_id INT UNSIGNED UNIQUE KEY,
damage_done INT UNSIGNED,
item_level INT UNSIGNED
)engine=InnoDB CHARSET utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE players(
player_id INT UNSIGNED auto_increment PRIMARY KEY,
name VARCHAR(20)
)engine=InnoDB CHARSET utf8mb4 COLLATE = utf8mb4_unicode_ci;

expected: The outer join of players and reports_players to display a player's name as well as their damage dealt. actual: ERROR 1066(42000): Not unique table/alias: 'rp'

Answer 1

If your not dead set on showing your values inline, the below should give you a report of distinct player names, and the damage they have done.

SELECT p.name, sum(rp.damage_done) damage_done
FROM players p 
JOIN reports_players rp
    ON p.player_id=rp.player_id
Group By p.name

If you need all players, even if they have not done damage:

SELECT p.name, sum(ifnull(rp.damage_done,0)) damage_done
FROM players p 
LEFT JOIN reports_players rp
    ON p.player_id=rp.player_id
Group By p.name

To fix your query to a full outer join, you need to do the following:

SELECT p.name, sum(rp.damage_done)
FROM players p
LEFT JOIN reports_players rp ON p.player_id=rp.player_id
GROUP BY p.name
UNION
SELECT p.name, sum(rp.damage_done)
FROM reports_players rp
RIGHT JOIN players p ON p.player_id=rp.player_id
GROUP BY p.name;

But I don't really think that will solve your problem if the left joins are not working for you. I think the reason the above selects are not working for you as the data that is in your tables is not matching up. To show you that the selects that I made do work I make the below example.

-- make the tables. 
CREATE TABLE reports(
report_id INT UNSIGNED auto_increment PRIMARY KEY,
zone INT UNSIGNED,
report_url VARCHAR(20),
title VARCHAR(50)
)engine=InnoDB CHARSET utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- note that I removed the unique keys from this table on report_id and player_id.  
CREATE TABLE reports_players(
id INT UNSIGNED auto_increment PRIMARY KEY,
report_id INT UNSIGNED ,
player_id INT UNSIGNED ,
damage_done INT UNSIGNED,
item_level INT UNSIGNED
)engine=InnoDB CHARSET utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE players(
player_id INT UNSIGNED auto_increment PRIMARY KEY,
name VARCHAR(20)
)engine=InnoDB CHARSET utf8mb4 COLLATE = utf8mb4_unicode_ci;

create index reports_players_player_id on reports_players (player_id);
create index reports_players_report_id on reports_players (report_id);

insert into players (name) 
values 
('last one'),
('Jack'),
('Jaragoth'),
('Jim'),
('Joe'),
('Jill');

insert into reports (zone, report_url, title) VALUES
(1,'www.place.com','The Place'),
(2,'www.theother.com','The Other Place');


drop PROCEDURE if EXISTS makeData;
-- make a procedure to build a data set. 
CREATE PROCEDURE makeData() 
BEGIN
    DECLARE i INT DEFAULT 0;

    TRUNCATE TABLE reports_players;

    start transaction;
    WHILE i < 10000 DO
    insert into reports_players (report_id, player_id, damage_done, item_level)    
    select floor(rand() *2) + 1 report_id, floor(rand() * 5) + 1 player_id, floor(rand() *20) +1 damage_done, floor(rand() * 5) + 1 item_level;
    set i = (i + 1);
    END WHILE;
    commit;
END ;

-- make the data
call makeData();

-- we will want an index
create index reports_players_player_id on reports_players (player_id);
create index reports_players_report_id on reports_players (report_id);

-- prove we made the data
select count(1) from reports_players;

---- some reporting examples. 
-- Inner join
SELECT p.name, sum(ifnull(rp.damage_done,0)) damage_done
FROM players p 
JOIN reports_players rp
    ON p.player_id=rp.player_id
Group By p.name

-- Left outer join (all players regarless of if they have damaged)
SELECT p.name, sum(ifnull(rp.damage_done,0)) damage_done
FROM players p 
LEFT JOIN reports_players rp
    ON p.player_id=rp.player_id
Group By p.name

-- Left outer join grouped by player, and item
SELECT p.name, rp.item_level, sum(ifnull(rp.damage_done,0)) damage_done
FROM players p 
LEFT JOIN reports_players rp
    ON p.player_id=rp.player_id
Group By p.name, rp.item_level