[Solved] Filter table before counting records on junction tables

[Doranwen]

A few years ago I created a database to store information on my favorite fanfics. As part of it, there is a table Fanfics, a table Themes, and a junction table between the two (because a fic can have multiple themes and a theme can appear in multiple fics).

I had some help coming up with a query that would let me count the number of fics a theme was tagged in:

Code: Select all

SELECT "tm"."ThemeID", "tm"."Theme", COUNT( "tm"."Theme" ) "Count"
FROM "Fanfics" "fa"
LEFT JOIN "Fanfic_Theme" "ft"
ON "ft"."FanficID" = "fa"."FanficID"
LEFT JOIN "Themes" "tm"
ON "tm"."ThemeID" = "ft"."ThemeID"
GROUP BY "tm"."ThemeID", "tm"."Theme"
ORDER BY "Count" DESC, "tm"."Theme"

This works great. However, I'd like to tweak it to have it first filter the Fanfics table to limit it to the fics that are marked as favorites (Boolean field called Favorite in the Fanfics table). Try as I might, I can't figure out how to filter it first (I know the line `WHERE "Fanfics"."Favorite" = 1` but I suspect it's a bit more complicated than that since I can't figure out where that would go and actually *work*), then have it count the records on the junction table that match only those ids in the filtered list.

[robleyd]

Did you try

Code: Select all

FROM "Fanfics" "fa"
WHERE "Fanfics"."Favorite" = 1
LEFT JOIN "Fanfic_Theme" "ft"

[Doranwen]

Did you try

Code: Select all

FROM "Fanfics" "fa"
WHERE "Fanfics"."Favorite" = 1
LEFT JOIN "Fanfic_Theme" "ft"

Absolutely. That's why I said I couldn't figure out where it would go and actually work (I'd tested that and it threw errors at me). It appears to be more complicated than that.

[UnklDonald418]

It appears your title describes exactly what you need to do. Create a Filter

Code: Select all

 SELECT * FROM "Fanfics" WHERE "Fanfics"."Favorite" = 1

Use that in place of the Table "Fanfics" as a subQuery
Try

Code: Select all

SELECT "tm"."ThemeID", "tm"."Theme", COUNT( "tm"."Theme" ) "Count"
FROM ( SELECT * FROM "Fanfics" WHERE "Fanfics"."Favorite" = 1) "fa"
LEFT JOIN "Fanfic_Theme" "ft"
ON "ft"."FanficID" = "fa"."FanficID"
LEFT JOIN "Themes" "tm"
ON "tm"."ThemeID" = "ft"."ThemeID"
GROUP BY "tm"."ThemeID", "tm"."Theme"
ORDER BY "Count" DESC, "tm"."Theme"

[Doranwen]

Sweet, that worked perfectly - thank you! I'll have to remember that trick.