saper · June 9, 2017 01:03
diff --git a/Related Clubs.sql b/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
	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
No results found