Getting Related Clubs with Data Science Club (`125525`) for Test

Related Clubs.sql

SELECT subquery2.club"clubId", C.clubname"clubName", subquery2.count"relevanceScore"
FROM
    (
        -- [x] For each club: get all keywords for club
        SELECT K.club, count(subquery1.id)"count"--, Z.keyword -- not needed in GROUP BY
        --SELECT count(subquery1.id), K.club --aggregate, for GROUP BY
        FROM
            (
                -- this retrieves the keywords from club to subquery1.id in parent query
                SELECT K.id

                FROM clubs AS C
                RIGHT JOIN keytoclub AS K
                ON C.clubid=K.club
                WHERE C.clubid::INT=125525
                ORDER BY C.clubname ASC
            ) subquery1
        LEFT JOIN keytoclub AS K
        ON subquery1.id=K.id
        --GROUP BY K.club
        LEFT JOIN clubs AS C
        ON K.club::INT=C.clubid::INT
        --LEFT JOIN keyworddata AS Z
        --ON subquery1.id=Z.id
        WHERE K.club::INT<>125525
        GROUP BY K.club
        ORDER BY count DESC, club::INT ASC
    ) subquery2
LEFT JOIN clubs AS C
ON subquery2.club=C.clubid

Here's what I am getting for my data:

primary	secondary	relevance_score
Accounting Society	National Society of Black Engineers	1
Accounting Society	Society of Hispanic Professional Engineers	1
American Marketing Association	American Water Resources Association	2
American Marketing Association	Association for Computing Machinery	1
American Marketing Association	Association of Latino Professionals For America	1
American Marketing Association	Construction Management Student Association	1
American Marketing Association	Management Information Systems Association	1
American Marketing Association	Sacramento Association of Collegiate Entrepreneurs	1
American Water Resources Association	American Marketing Association	2
American Water Resources Association	Association for Computing Machinery	1
American Water Resources Association	Association of Latino Professionals For America	1
American Water Resources Association	Construction Management Student Association	1
American Water Resources Association	Management Information Systems Association	1
American Water Resources Association	Sacramento Association of Collegiate Entrepreneurs	1
Association for Computing Machinery	American Marketing Association	1
Association for Computing Machinery	American Water Resources Association	1
Association for Computing Machinery	Association of Latino Professionals For America	2
Association for Computing Machinery	Construction Management Student Association	1
Association for Computing Machinery	Management Information Systems Association	1
Association for Computing Machinery	Sacramento Association of Collegiate Entrepreneurs	1
Association of Latino Professionals For America	American Marketing Association	1
Association of Latino Professionals For America	American Water Resources Association	1
Association of Latino Professionals For America	Association for Computing Machinery	2
Association of Latino Professionals For America	Construction Management Student Association	1
Association of Latino Professionals For America	Management Information Systems Association	1
Association of Latino Professionals For America	National Society of Black Engineers	1
Association of Latino Professionals For America	Sacramento Association of Collegiate Entrepreneurs	2
Association of Latino Professionals For America	Society of Hispanic Professional Engineers	1
Construction Management Student Association	American Marketing Association	1
Construction Management Student Association	American Water Resources Association	1
Construction Management Student Association	Association for Computing Machinery	1
Construction Management Student Association	Association of Latino Professionals For America	1
Construction Management Student Association	Management Information Systems Association	2
Construction Management Student Association	Sacramento Association of Collegiate Entrepreneurs	1
Data Science Club	Math Club	1
Management Information Systems Association	American Marketing Association	1
Management Information Systems Association	American Water Resources Association	1
Management Information Systems Association	Association for Computing Machinery	1
Management Information Systems Association	Association of Latino Professionals For America	1
Management Information Systems Association	Construction Management Student Association	2
Management Information Systems Association	Sacramento Association of Collegiate Entrepreneurs	1
Math Club	Data Science Club	1
National Society of Black Engineers	Accounting Society	1
National Society of Black Engineers	Association of Latino Professionals For America	1
National Society of Black Engineers	Sacramento Association of Collegiate Entrepreneurs	1
National Society of Black Engineers	Society of Hispanic Professional Engineers	3
Sacramento Association of Collegiate Entrepreneurs	American Marketing Association	1
Sacramento Association of Collegiate Entrepreneurs	American Water Resources Association	1
Sacramento Association of Collegiate Entrepreneurs	Association for Computing Machinery	1
Sacramento Association of Collegiate Entrepreneurs	Association of Latino Professionals For America	2
Sacramento Association of Collegiate Entrepreneurs	Construction Management Student Association	1
Sacramento Association of Collegiate Entrepreneurs	Management Information Systems Association	1
Sacramento Association of Collegiate Entrepreneurs	National Society of Black Engineers	1
Sacramento Association of Collegiate Entrepreneurs	Society of Hispanic Professional Engineers	1
Society of Hispanic Professional Engineers	Accounting Society	1
Society of Hispanic Professional Engineers	Association of Latino Professionals For America	1
Society of Hispanic Professional Engineers	National Society of Black Engineers	3
Society of Hispanic Professional Engineers	Sacramento Association of Collegiate Entrepreneurs	1