You can't join both tables in one (non-sub-)query. Let's say first table contains 3 occurances for one player id and the second contains 3. After the first join, you have 3 rows with that player id, each of which will join against 3 rows in the second table for the same id. Thus, you will get 3*3 rows = 9 rows, which are then grouped.
You can however do it using a subquery. Let the inner query deal with one of the tables, using count and group by. Then let the outer query do the same on the other table, and then also join on the subquery (which must be given an alias).
CREATE TABLE player (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(10)
);
CREATE TABLE full (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
player INT UNSIGNED NOT NULL,
FOREIGN KEY (player) REFERENCES player(id)
);
CREATE TABLE partial (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
player INT UNSIGNED NOT NULL,
FOREIGN KEY (player) REFERENCES player(id)
);
INSERT INTO player(name) VALUES
('Alpha'),
('Bravo'),
('Charlie'),
('Delta');
INSERT INTO full(player) VALUES
(1),
(4),
(1),
(1),
(2),
(2),
(2),
(4);
INSERT INTO partial(player) VALUES
(3),
(1),
(3),
(2),
(2),
(2);
SELECT p.id, p.name, COUNT(part.id) as part, t.full, COUNT(part.id) + t.full AS part_and_full
FROM player p
LEFT JOIN partial part ON p.id = part.player
LEFT JOIN
(
SELECT p.id, COUNT(f.id) as full
FROM player p
LEFT JOIN full f ON p.id = f.player
GROUP BY p.id
) t ON t.id = p .id
GROUP BY p.id, p.name;
