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)
;
- 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)
;
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
;
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)
;
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)
;
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)
;
If anyone knows the answer on referencing query within another, let me know - but for now I'll mark this solved.