Apache OpenOffice Community Forum

Posted: **Sun Jul 27, 2025 11:31 am**

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.

Posted: **Sun Jul 27, 2025 1:29 pm**

Did you try

Code: Select all

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

Posted: **Sun Jul 27, 2025 7:19 pm**

robleyd wrote: ↑Sun Jul 27, 2025 1:29 pm 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.

Posted: **Thu Jul 31, 2025 6:07 am**

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"

Posted: **Sat Aug 02, 2025 5:14 am**

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

Apache OpenOffice Community Forum

[Solved] Filter table before counting records on junction tables

[Solved] Filter table before counting records on junction tables

Re: Filter table before counting records on junction tables

Re: Filter table before counting records on junction tables

Re: Filter table before counting records on junction tables

Re: Filter table before counting records on junction tables