MySQL - FULL JOIN inside Subquery

Question

I know this is similar to How to do a FULL OUTER JOIN in MySQL? but i'm using it in a subquery

I'm trying to get the Total Plays (number of coincidences of Beatmaps.id in Rankings.beatmapID) including Beatmaps with 0 plays

My actual code always return 1 in the TotalPlays row...

BeatmapSetID    BeatmapID BeatmapSetCreatorUsername TotalPlays
     1             11              Legend               1
     1             12              Legend               1
     2             13              Legend               1
     2             14              Legend               1
     3             15              Darkar               1

Expected result:

BeatmapSetID    BeatmapID BeatmapSetCreatorUsername TotalPlays
     1             11              Legend               0
     1             12              Legend               2
     2             13              Legend               3
     2             14              Legend               2
     3             15              Darkar               1

What i'm doing wrong?

This is my code:

SELECT
        BeatmapSets.id AS BeatmapSetID,
        Beatmaps.id AS BeatmapID,
        Account.username AS BeatmapSetCreatorUsername,

    (SELECT COUNT(plays) FROM
     (SELECT Rankings.beatmapID, COUNT(Rankings.id) as plays FROM Beatmaps 
       LEFT OUTER JOIN Rankings ON Beatmaps.id = Rankings.beatmapID
    
     UNION
     
     SELECT Rankings.beatmapID, COUNT(Rankings.id) as plays FROM Beatmaps 
       RIGHT OUTER JOIN Rankings ON Beatmaps.id = Rankings.beatmapID
     ) Rankings
    ) AS TotalPlays

    FROM BeatmapSets AS BeatmapSets

    INNER JOIN Beatmaps AS Beatmaps ON BeatmapSets.id = Beatmaps.setID
    INNER JOIN Account AS Account ON BeatmapSets.creatorID = Account.id

Sample data:

SET sql_mode = '';
SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

CREATE TABLE Rankings (
  `id` INT NOT NULL,
  `userID` INT NOT NULL,
  `beatmapID` INT NOT NULL,
  `score` INT NOT NULL
);

INSERT INTO Rankings (id, userID, beatmapID, score) VALUES (1, 1, 12, 500);
INSERT INTO Rankings (id, userID, beatmapID, score) VALUES (2, 1, 12, 1000);
                                    
INSERT INTO Rankings (id, userID, beatmapID, score) VALUES (3, 1, 13, 1000);
                                    
INSERT INTO Rankings (id, userID, beatmapID, score) VALUES (4, 2, 13, 1000);
INSERT INTO Rankings (id, userID, beatmapID, score) VALUES (5, 2, 13, 2000);       
                                    
INSERT INTO Rankings (id, userID, beatmapID, score) VALUES (6, 1, 14, 2000);                                   
INSERT INTO Rankings (id, userID, beatmapID, score) VALUES (7, 2, 14, 3000); 
                                    
INSERT INTO Rankings (id, userID, beatmapID, score) VALUES (8, 2, 15, 1000);                                      
                                    
CREATE TABLE Account (
  `id` INT NOT NULL,
  `username` varchar(50) NOT NULL
);

INSERT INTO Account (id, username) VALUES (1, "Legend");
INSERT INTO Account (id, username) VALUES (2, "Darkar");

CREATE TABLE BeatmapSets (
  `id` INT NOT NULL,
  `creatorID` INT NOT NULL
);

INSERT INTO BeatmapSets (id, creatorID) VALUES (1, 1);
INSERT INTO BeatmapSets (id, creatorID) VALUES (2, 1);
INSERT INTO BeatmapSets (id, creatorID) VALUES (3, 2);

CREATE TABLE Beatmaps (
  `id` INT NOT NULL,
  `setID` INT NOT NULL
);

INSERT INTO Beatmaps (id, setID) VALUES (11, 1);
INSERT INTO Beatmaps (id, setID) VALUES (12, 1);
INSERT INTO Beatmaps (id, setID) VALUES (13, 2);
INSERT INTO Beatmaps (id, setID) VALUES (14, 2);
INSERT INTO Beatmaps (id, setID) VALUES (15, 3);

Fiddle of same: https://www.db-fiddle.com/f/3Y5M7dsfeLDGPc23kimDPZ/4

Hi, thanks for the info, i created it here: https://www.db-fiddle.com/f/3Y5M7dsfeLDGPc23kimDPZ/2 — Arturo Rodriguez, Jul 22 '20 at 06:27
Hi, the "BeatmapSet" can be repeated, but not the Songs.id (BeatmapID) — Arturo Rodriguez, Jul 22 '20 at 06:37
I'm sorry. A user can rank the same song multiple times because every time he play the song, the score is saved, this is how he make a "new play" in the song. I want to count the total plays of every song. — Arturo Rodriguez, Jul 22 '20 at 07:02
So if a Song has 1000 Rankings (plays) from 1 User and 100 Plays of another User then the Song has 1100 plays. — Arturo Rodriguez, Jul 22 '20 at 07:04
Hi, updated the fiddle and the post to match exactly and clarify what i'm doing. https://www.db-fiddle.com/f/3Y5M7dsfeLDGPc23kimDPZ/4 — Arturo Rodriguez, Jul 22 '20 at 07:32
Isn't this what you are looking for `(SELECT COUNT(Rankings.id) FROM Rankings WHERE Rankings.beatmapID = Beatmaps.id) AS TotalPlays` (replace the current inner _FULL JOIN_ query) — Fullslack, Jul 22 '20 at 07:50

score 1 · Accepted Answer · answered Jul 22 '20 at 08:02

1

Replacing the inner FULL JOIN with the following code seems to solve your problem:

(SELECT COUNT(Rankings.id) FROM Rankings WHERE Rankings.beatmapID = Beatmaps.id) AS TotalPlays

With the following DB Fiddle to demonstrate result.

answered Jul 22 '20 at 08:02

Fullslack

290
1
2
11

MySQL - FULL JOIN inside Subquery

1 Answers1