[Solved] Filter query by count on only one table

[Doranwen]

So I've been working with my fanfic database, which is a split database with HSQLDB 2.5.1 as the backend, LibreOffice Base as the frontend. It has a good number of tables and junction tables in order to relate everything together properly. (There are regular tables for fanfics, fandoms, authors, major and minor characters, ships, etc., and the junction tables are named very straightforwardly, as a combo of the names of the two tables they're connecting.)

The below query is a result from my last question, and pulls all of the fics which are not marked dead, groups fandoms together, authors together, ships together, and lumps each into a single entry per fic.

Code: Select all

select
	group_concat(distinct fandom order by fandom separator ', ') as fandoms,
	group_concat(distinct author order by upper(author) separator ', ') as authors,
	title,
	wordcount,
	rating,
	link,
	group_concat(distinct ship separator ', ') as ships
from (
select
	"Fanfics"."FanficID" as id,
	"Fandoms"."Fandom" as fandom,
	"Authors"."Author" as author,
	"Fanfics"."Title" as title,
	"Fanfics"."Wordcount" as wordcount,
	"Fanfics"."Rating" as rating,
	"Fanfics"."WebLink" as link,
	ifnull("Ships"."Ship", 'no major ships') as ship
from
	"Fanfics",
	"Fanfic_Author", "Authors",
	"Fanfic_Fandom", "Fandoms",
	"Crossovers_IDs"
left join
	"Fanfic_Ships" on "Fanfic_Ships"."FanficID" = "Fanfics"."FanficID"
left join
	"Ships" on "Fanfic_Ships"."ShipID" = "Ships"."ShipID"
where
	    "Fanfic_Author"."FanficID" = "Fanfics"."FanficID"
	and "Fanfic_Author"."AuthorID" = "Authors"."AuthorID"
	and "Fanfic_Fandom"."FanficID" = "Fanfics"."FanficID"
	and "Fanfic_Fandom"."FandomID" = "Fandoms"."FandomID"
	and "Fanfics"."Dead" = 0
)
group by
	id, title, wordcount, rating, link
order by
	fandoms, upper(authors), upper(title)
;

It works great! I love it. I've figured out how to modify it to limit by other Boolean fields, or even by the date the record was created. Tonight I used it to filter by a single fandom (and "Fanfic_Fandom"."FandomID" = 1), and here's where I realized there are two different things I need to be able to modify the above query to do:

- to limit the results to ONLY crossovers (fics which have more than one fandom; i.e. the records where FanficID appears in Fanfic_Fandom more than once, for any fandoms)
- to EXCLUDE crossovers (only display fics in a particular fandom where the FanficID does *not* appear in Fanfic_Fandom more than once)

Obviously both of those are mutually exclusive, so I won't be combining them in a single query, but I need to figure out how to do each separately. I've used COUNT before, but only with queries where the final result was the count numbers. Here, I need to use COUNT as one of the conditions. An online SQL tutorial said "To filter the groups by the result of the COUNT(*) function, we need to use the COUNT(*) function in the HAVING clause." I tried that as below, but I found that it included both crossovers *and* any fic which had *more than one ship* or *more than one author*. I'm clearly not filtering it properly, since it's looking for the FanficID occurring more than once in *any* of the tables, which includes not only the Fanfic_Fandom table but also the Fanfic_Ships and Fanfic_Authors junction tables.

Code: Select all

select
	group_concat(distinct fandom order by fandom separator ', ') as fandoms,
	group_concat(distinct author order by upper(author) separator ', ') as authors,
	title,
	wordcount,
	rating,
	link,
	group_concat(distinct ship separator ', ') as ships,
	count(ficid)
from (
select
	"Fanfics"."FanficID" as id,
	"Fanfic_Fandom"."FanficID" as ficid,
	"Fandoms"."Fandom" as fandom,
	"Authors"."Author" as author,
	"Fanfics"."Title" as title,
	"Fanfics"."Wordcount" as wordcount,
	"Fanfics"."Rating" as rating,
	"Fanfics"."WebLink" as link,
	ifnull("Ships"."Ship", 'no major ships') as ship
from
	"Fanfics",
	"Fanfic_Author", "Authors",
	"Fanfic_Fandom", "Fandoms"
left join
	"Fanfic_Ships" on "Fanfic_Ships"."FanficID" = "Fanfics"."FanficID"
left join
	"Ships" on "Fanfic_Ships"."ShipID" = "Ships"."ShipID"
where
	    "Fanfic_Author"."FanficID" = "Fanfics"."FanficID"
	and "Fanfic_Author"."AuthorID" = "Authors"."AuthorID"
	and "Fanfic_Fandom"."FanficID" = "Fanfics"."FanficID"
	and "Fanfic_Fandom"."FandomID" = "Fandoms"."FandomID"
	and "Fanfics"."Dead" = 0
)
group by
	id, title, wordcount, rating, link
having
	count(ficid) > 1
order by
	fandoms, upper(authors), upper(title)
;

All of the searches I did turned up things that were not very close to this. How do I get it to count only the records with multiple FanficID instances in *Fanfic_Fandom*, but *not* the records with multiple FanficID instances in Fanfic_Ships or Fanfic_Authors? If I weren't needing to include the ships, and didn't have any multi-authored fics, this would be a lot simpler (I'm pretty sure it would work), but I do need to include the ships and there are some fics with multiple authors, unfortunately. My suspicion is that i need some kind of join somewhere, but I'm not sure exactly how to set it up…

Edit: So a friend helped me come up with this query to pull just the ID numbers (and count) of the crossover fics.

Code: Select all

Select
    "Fanfic_Fandom"."FanficID",
    Count("Fandoms"."Fandom")
from
   "Fanfic_Fandom", "Fandoms"
where
    "Fanfic_Fandom"."FandomID" = "Fandoms"."FandomID"
Group by
    FanficID
Having
    count("Fandoms"."Fandom") > 1
;

What we can't figure out is how to reference that in the other query. If I try to put in as a subquery with an inner join, I get a weird error saying I have a missing AS in a line that shouldn't need an AS at all!

Code: Select all

select
	group_concat(distinct fandom order by fandom separator ', ') as fandoms,
	group_concat(distinct author order by upper(author) separator ', ') as authors,
	title,
	wordcount,
	rating,
	link,
	group_concat(distinct ship separator ', ') as ships
from (
select
	"Fanfics"."FanficID" as id,
	"Fandoms"."Fandom" as fandom,
	"Authors"."Author" as author,
	"Fanfics"."Title" as title,
	"Fanfics"."Wordcount" as wordcount,
	"Fanfics"."Rating" as rating,
	"Fanfics"."WebLink" as link,
	ifnull("Ships"."Ship", 'no major ships') as ship,
	"Fanfic_Fandom"."FandomID" as fandomid
from
	"Fanfics",
	"Fanfic_Author", "Authors",
	"Fanfic_Fandom", "Fandoms"
left join
	"Fanfic_Ships" on "Fanfic_Ships"."FanficID" = "Fanfics"."FanficID"
left join
	"Ships" on "Fanfic_Ships"."ShipID" = "Ships"."ShipID"
inner join
    (	(Select "Fanfic_Fandom"."FanficID" as FanficID,
		Count("Fandoms"."Fandom")
		from "Fanfic_Fandom", "Fandoms"
		where "Fanfic_Fandom"."FandomID" = "Fandoms"."FandomID"
		Group by FanficID
		Having count("Fandoms"."Fandom") > 1) as Q1) on "Fanfics"."FanficID" = Q1."FanficID"
where
	    "Fanfic_Author"."FanficID" = "Fanfics"."FanficID"
	and "Fanfic_Author"."AuthorID" = "Authors"."AuthorID"
	and "Fanfic_Fandom"."FanficID" = "Fanfics"."FanficID"
	and "Fanfic_Fandom"."FandomID" = "Fandoms"."FandomID"
	and "Fanfics"."Dead" = 0
)
group by
	id, title, wordcount, rating, link
having
	count(fandomid) > 1
order by
	fandoms, upper(authors), upper(title)
;

It wants an AS in this line: " and "Fanfic_Fandom"."FanficID" = "Fanfics"."FanficID"" Like, what???

My friend suggested calling the query field the way I do a table, but I keep getting "user lacks privilege or object not found: Crossovers_IDs". Below is how I attempted that:

Code: Select all

select
	group_concat(distinct fandom order by fandom separator ', ') as fandoms,
	group_concat(distinct author order by upper(author) separator ', ') as authors,
	title,
	wordcount,
	rating,
	link,
	group_concat(distinct ship separator ', ') as ships
from (
select
	"Fanfics"."FanficID" as id,
	"Fandoms"."Fandom" as fandom,
	"Authors"."Author" as author,
	"Fanfics"."Title" as title,
	"Fanfics"."Wordcount" as wordcount,
	"Fanfics"."Rating" as rating,
	"Fanfics"."WebLink" as link,
	ifnull("Ships"."Ship", 'no major ships') as ship,
	"Crossovers_IDs"."FanficID"
from
	"Fanfics",
	"Fanfic_Author", "Authors",
	"Fanfic_Fandom", "Fandoms"
left join
	"Fanfic_Ships" on "Fanfic_Ships"."FanficID" = "Fanfics"."FanficID"
left join
	"Ships" on "Fanfic_Ships"."ShipID" = "Ships"."ShipID"
inner join
    "Crossovers_IDs" on "Fanfics"."FanficID" = "Crossovers_IDs"."FanficID"
where
	    "Fanfic_Author"."FanficID" = "Fanfics"."FanficID"
	and "Fanfic_Author"."AuthorID" = "Authors"."AuthorID"
	and "Fanfic_Fandom"."FanficID" = "Fanfics"."FanficID"
	and "Fanfic_Fandom"."FandomID" = "Fandoms"."FandomID"
	and "Fanfics"."Dead" = 0
)
group by
	id, title, wordcount, rating, link
order by
	fandoms, upper(authors), upper(title)
;

I've tried adding "Crossovers_IDs" to the from after "Fandoms", I've tried doing a left join, I've tried adding "and "Fanfics"."FanficID" = "Crossovers_IDs"."FanficID"" to the where section… nothing I try seems to work.

EDIT #2: I figured out my issue when I tried to put the query directly in - I was trying to alias the whole query instead of the ID number that needed syncing. The below query WORKS:

Code: Select all

select
	group_concat(distinct fandom order by fandom separator ', ') as fandoms,
	group_concat(distinct author order by upper(author) separator ', ') as authors,
	title,
	wordcount,
	rating,
	link,
	group_concat(distinct ship separator ', ') as ships
from (
select
	"Fanfics"."FanficID" as id,
	"Fandoms"."Fandom" as fandom,
	"Authors"."Author" as author,
	"Fanfics"."Title" as title,
	"Fanfics"."Wordcount" as wordcount,
	"Fanfics"."Rating" as rating,
	"Fanfics"."WebLink" as link,
	ifnull("Ships"."Ship", 'no major ships') as ship
from
	"Fanfics",
	"Fanfic_Author", "Authors",
	"Fanfic_Fandom", "Fandoms"
left join
	"Fanfic_Ships" on "Fanfic_Ships"."FanficID" = "Fanfics"."FanficID"
left join
	"Ships" on "Fanfic_Ships"."ShipID" = "Ships"."ShipID"
inner join
    (Select
    	"Fanfic_Fandom"."FanficID" as ficid,
    	Count("Fandoms"."Fandom")
	from
	   "Fanfic_Fandom", "Fandoms"
	where
	    "Fanfic_Fandom"."FandomID" = "Fandoms"."FandomID"
	Group by
	    "Fanfic_Fandom"."FanficID"
	Having
	    count("Fandoms"."Fandom") > 1) on "Fanfics"."FanficID" = ficid
where
	    "Fanfic_Author"."FanficID" = "Fanfics"."FanficID"
	and "Fanfic_Author"."AuthorID" = "Authors"."AuthorID"
	and "Fanfic_Fandom"."FanficID" = "Fanfics"."FanficID"
	and "Fanfic_Fandom"."FandomID" = "Fandoms"."FandomID"
	and "Fanfics"."Dead" = 0
)
group by
	id, title, wordcount, rating, link
order by
	fandoms, upper(authors), upper(title)
;

Still have no clue how one references a query within another one (is it even possible? Is that where the View thing comes in?) but hey, I got this one working at least. XD And to exclude crossovers, all I have to do is set it = 1 instead of > 1.

If anyone knows the answer on referencing query within another, let me know - but for now I'll mark this solved.

[chrisb]

if i have correctly understood the issue i do not think that you should be having a problem with this.
the first line in the FROM clause takes care of the filtration, use any 1 of the 2 lines of code below according to your needs.

Code: Select all

(select "FanficID" from "Fanfic_Fandom" group by "FanficID" having count("FandomID") > 1) ff_filtered --fics with multipe fandoms
(select "FanficID" from "Fanfic_Fandom" group by "FanficID" having count("FandomID") = 1) ff_filtered --fics without multipe fandoms

unlike yourself i do not like your code so i have copied this from a previous post & made the necessary alterations.
it may produce syntax or other errors, i cannot test.

Code: Select all

select
  group_concat(distinct dom."Fandom" order by dom."Fandom" separator ', ') fandoms,
  group_concat(distinct a."Author" order by upper(a."Author") separator ', ') authors,
  f."Title" title,
  f."Wordcount" wordcount,
  f."Rating" rating,
  f."WebLink" link,
  group_concat(distinct coalesce(s."Ship", 'No Ships') order by s."Ship" separator ', ') ships
from --use any 1 of the 2 lines below
	(select "FanficID" from "Fanfic_Fandom" group by "FanficID" having count("FandomID") > 1) ff_filtered --fics with multipe fandoms
--	(select "FanficID" from "Fanfic_Fandom" group by "FanficID" having count("FandomID") = 1) ff_filtered --fics without multipe fandoms
join "Fanfic_Fandom" ff on ff_filtered."FanficID" = ff."FanficID"
join
(
  select "FanficID", "Title", "Wordcount", "Rating", "WebLink"
  from "Fanfics"
  where "Dead" = 0
) f on f."FanficID" = ff."FanficID"
join "Fanfic_Author" fa on f."FanficID" = fa."FanficID"
join "Authors" a on fa."AuthorID" = a."AuthorID"
join "Fandoms" dom on ff."FandomID" = dom."FandomID"
left join "Fanfic_Ships" fs on f."FanficID" = fs."FanficID"
left join "Ships" s on fs."ShipID" = s."ShipID"
group by f."FanficID", f."Title", f."Wordcount", f."Rating", f."WebLink"
order by fandoms, authors, title

[Doranwen]

Huh, I wouldn't have thought to count the *FandomID* - but the code does work! Thanks! That looks like a shorter and more efficient way to deal with this particular one.

LOL, I only like the code I had because it works. I am not particular one way or the other, which I use. I am dealing with very complicated queries (to me, at least - and even my mom thinks so, but then she's used to working in Access and building the queries visually, not with pure SQL) so I've mostly copied/pasted one query to another and tweaked, or copied whatever someone came up with that worked, and as a result some queries have likely gotten larger than necessary.

This query works excellently for pulling all the crossovers, though simply reversing the > 1 and = 1 in this one won't fully do what I need. You see, I had set it to pull all of the fanfics for *one or more specific fandoms* that were not crossovers, using this line under where, for an example (this one pulls only the fics in specific fandoms I knew had very very few fics in them):

Code: Select all

and "Fanfic_Fandom"."FandomID" IN (3,9,18,20,21,22,23,24,25,27,30)

Changing to the = 1 forces it to only select non-crossovers, and I think I figured out where to insert the necessary fandom-filtering here, using your query:

Code: Select all

    select
      group_concat(distinct dom."Fandom" order by dom."Fandom" separator ', ') fandoms,
      group_concat(distinct a."Author" order by upper(a."Author") separator ', ') authors,
      f."Title" title,
      f."Wordcount" wordcount,
      f."Rating" rating,
      f."WebLink" link,
      group_concat(distinct coalesce(s."Ship", 'no major ships') order by s."Ship" separator ', ') ships
    from
       (select "FanficID" from "Fanfic_Fandom" group by "FanficID" having count("FandomID") = 1) ff_filtered
    join "Fanfic_Fandom" ff on ff_filtered."FanficID" = ff."FanficID"
    join
    (
      select "FanficID", "Title", "Wordcount", "Rating", "WebLink"
      from "Fanfics"
      where "Dead" = 0
    ) f on f."FanficID" = ff."FanficID"
    join "Fanfic_Author" fa on f."FanficID" = fa."FanficID"
    join "Authors" a on fa."AuthorID" = a."AuthorID"
    join "Fandoms" dom on ff."FandomID" = dom."FandomID"
    left join "Fanfic_Ships" fs on f."FanficID" = fs."FanficID"
    left join "Ships" s on fs."ShipID" = s."ShipID"
    where ff."FandomID" IN (3,9,18,20,21,22,23,24,25,27,30)
    group by f."FanficID", f."Title", f."Wordcount", f."Rating", f."WebLink"
    order by fandoms, authors, title

[chrisb]

it seems that you want to limit your selection to fics which have single/specific fandoms as shown in the above query?
so the only issue now is where to insert the WHERE clause.
i suggest replacing:

Code: Select all

join "Fanfic_Fandom" ff on ff_filtered."FanficID" = ff."FanficID"

with:

Code: Select all

join (select * from "Fanfic_Fandom" where "FandomID" IN(3,9,18,20,21,22,23,24,25,27,30)) ff on ff_filtered."FanficID" = ff."FanficID"

the FROM clause in our code constructs a table (columns & rows) in memory which reflects our selected records.
a large derived table will require more memory & processor time than a smaller derived table.
to aid query execution it's wise to restrict our selection to only those fields & records which are essential.
using sub-queries in the FROM clause to select & filter data enhances query performance.

[Doranwen]

Ah! Right, OK. Thanks. Efficiency and performance are definitely areas I'm not so good at.

Yeah, this started because I was pulling records for single fandoms, and the first one I chose was perfectly fine (there were no crossovers involving it) but then I tried the batch of tiny fandoms, and realized that the first fic it pulled was a crossover - but I had no way of pulling JUST crossovers separately, or excluding crossovers from my other fandom selections. So this is definitely needed. Thank you again!