[Solved] Aggregate data using two junction tables

[Doranwen]

This is going to take a bit of explaining, lol. I'm working on my fanfic database, which has the main table Fanfics (containing primary key FanficID and a host of other specific fields), along with tables Fandoms, Authors, Chars, Ships, Themes, etc. (The primary keys for those have similar logical labels like FandomID, AuthorID, etc.) There are junction tables connecting most of these tables with Fanfics, and between the Authors and Ships table and Fandoms (the Chars table uses a foreign key to connect it to Fandoms).

Crucially, there are *two* junction tables connecting Fanfics and Chars, and Fanfics and Ships. For the ships, one is Fanfic_Ships, and the other is Fanfic_MShips (and the chars are named similarly). The data in these two junction tables is unique and never overlaps (a ship is never on both tables for the same fanfic), but I need to keep them separate for good reasons (they distinguish major vs. minor ships in a fic, a distinction that is very useful).

I've been using variations of the following query to pull data for a bunch of fanfics at once, aggregating all fandoms, authors, ships, and themes into a single field each.

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,
	group_concat(distinct theme separator ', ') as themes
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 ships') as ship,
	ifnull("Themes"."Theme", 'no themes') as theme
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"
left join
	"Fanfic_Theme" on "Fanfic_Theme"."FanficID" = "Fanfics"."FanficID"
left join
	"Themes" on "Fanfic_Theme"."ThemeID" = "Themes"."ThemeID"
where
	    "Fanfic_Author"."FanficID" = "Fanfics"."FanficID"
	and "Fanfic_Author"."AuthorID" = "Authors"."AuthorID"
	and "Fanfic_Fandom"."FanficID" = "Fanfics"."FanficID"
	and "Fanfic_Fandom"."FandomID" = "Fandoms"."FandomID"
)
group by
	id, title, wordcount, rating, link
order by
	fandoms, upper(authors), upper(title)
;

It doesn't currently pull data for Chars but seeing how the ships are done, I could easily modify it to make it do that. The problem is, I have absolutely no idea how to pull both the ships on Fanfic_Ships *and* Fanfic_MShips, and same for the Fanfic_Chars and Fanfic_MChars. The way the joining is set up, it connects the Ships table to Fanfics by looking at the pairs on Fanfic_Ships, leaving no way to join and aggregate the pairs on Fanfic_MShips as well. I'd love to be able to end up with separate columns for Ships, MShips, Chars, and MChars. Does anyone have any ideas on how to do this, or know what I need to look into?

[MrProgrammer]

You are more likely to get an answer when you provide a way to reproduce the situation with an attachment. No volunteer wants to attempt to replicate your database and create data for it to run some tests. Remember this advice for future topics since I won't repeat it there. It doesn't have to be your real data, but it should be data with both Fanfic_Ships and Fanfic_MShips which can be used for the query you gave.

You don't specify which database Base is connected to, so you risk receiving SQL that your database doesn't support.

It might be worthwhile to review your previous topics about junction tables to see if those have ideas that would help with the current topic.

[Doranwen]

I'm sorry, I genuinely didn't realize I was asking someone to test anything or give me an exact set of SQL. I'm trying to understand it for myself and was looking more for tips on how to think the SQL structure to solve a problem like this so I could try to do it myself, rather than asking someone to give me the exact query needed. I thought it would be less work for someone to point me in the right direction than for them to just hand me the SQL. I attempted to search for a solution but I couldn't find any combination of search terms that produced anything along the lines of what I was trying to do, so I had no idea what SQL topics to look into, and I tend to struggle to comprehend joins. But I suppose if I'm this stuck I need more extensive help than just "here's what to look at".

Apologies for forgetting to state that I'm using a standard hybrid HSQLDB with Base, the one with the linked wizard on here. I've now added it to my signature and included that here.

I've tried to get a copy of my database down to a size that would work on here, but I can't seem to get it smaller than 5 mb. (I backed up the main one and created a copy of this one elsewhere on my hdd, and deleted records and used SHUTDOWN COMPACT and it dropped from about 20 mb to 5 mb but won't get smaller than that. I've got it down to just 100 records - I have over 2200 in the full copy - and it's still at 5 mb.) I'm guessing the only solution is to create a brand new embedded database with all 18 tables and the same relationships between them, and enter a few preliminary fics just to have test data, because I can't see how one shrinks the hybrid version small enough. That will be a massive pile of work, oof. Will take me a bit to do; I'll start on that now, then.

I've looked at my previous queries but none of them involved connecting the same two tables with two different junction tables, and I could not see how any of them were similar to this. If they are and I'm missing it, that's precisely what I'm not seeing that I need pointed out to me.

Thank you for your help.

EDIT: I have edited an old version of an empty database to bring it up to match my current one, and added four fics into it, with a mix of as much variety as possible (at least one fic has multiple minor chars and multiple minor ships). That should hopefully work for people. It does have multiple macros embedded to make the forms work for adding or editing fics, but these should not impact queries and can be disabled by someone who is not planning on opening the forms in question.

[Doranwen]

Some friends elsewhere helped me somewhat and I stumbled onto the solution - aliasing the second table for the second join:

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,
	offlink,
	summary,
	group_concat(distinct ship separator ', ') as ships,
	group_concat(distinct mship separator ', ') as mships,
	group_concat(distinct char separator ', ') as chars,
	group_concat(distinct mchar separator ', ') as mchars,
	group_concat(distinct theme separator ', ') as themes,
	dead,
	favorite,
	aplus,
	wip,
	mf,
	ff,
	other,
	notes
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,
	"Fanfics"."Summary" as summary,
	"Fanfics"."Dead" as dead,
	"Fanfics"."Favorite" as favorite,
	"Fanfics"."Aplus" as aplus,
	"Fanfics"."WIP" as wip,
	"Fanfics"."M_F" as mf,
	"Fanfics"."F_F" as ff,
	"Fanfics"."Other" as other,
	"Fanfics"."Special Notes" as notes,
	"Fanfics"."Date" as day,
	"Fanfics"."OfflineLink" as offlink,
	ifnull("Ships"."Ship", 'no major ships') as ship,
	ifnull(ms."Ship", 'no minor ships') as mship,
	ifnull("Chars"."Char", 'no major characters') as char,
	ifnull(mc."Char", 'no minor characters') as mchar,
	ifnull("Themes"."Theme", 'no themes') as theme
from
	"Fanfics",
	"Fanfic_Author", "Authors",
	"Fanfic_Fandom", "Fandoms", "Times"
left join
	"Fanfic_Ships" on "Fanfic_Ships"."FanficID" = "Fanfics"."FanficID"
left join
	"Ships" on "Fanfic_Ships"."ShipID" = "Ships"."ShipID"
left join
	"Fanfic_MShips" on "Fanfic_MShips"."FanficID" = "Fanfics"."FanficID"
left join
	"Ships" ms on "Fanfic_MShips"."ShipID" = ms."ShipID"
left join
	"Fanfic_Chars" on "Fanfic_Chars"."FanficID" = "Fanfics"."FanficID"
left join
	"Chars" on "Fanfic_Chars"."CharID" = "Chars"."CharID"
left join
	"Fanfic_MChars" on "Fanfic_MChars"."FanficID" = "Fanfics"."FanficID"
left join
	"Chars" mc on "Fanfic_Chars"."CharID" = mc."CharID"
left join
	"Fanfic_Theme" on "Fanfic_Theme"."FanficID" = "Fanfics"."FanficID"
left join
	"Themes" on "Fanfic_Theme"."ThemeID" = "Themes"."ThemeID"
where
	    "Fanfic_Author"."FanficID" = "Fanfics"."FanficID"
	and "Fanfic_Author"."AuthorID" = "Authors"."AuthorID"
	and "Fanfic_Fandom"."FanficID" = "Fanfics"."FanficID"
	and "Fanfic_Fandom"."FandomID" = "Fandoms"."FandomID"
)
group by
	id, day, time, title, wordcount, rating, link, offlink, summary, dead, favorite, aplus, wip, mf, ff, other, notes
order by
	id, fandoms, ships, upper(authors), upper(title)
;