[SOLVED] Query excluding criteria, multiple junction tables

[Doranwen]

I asked for help awhile ago on building this database (which I got successfully), and I'm linking to that thread here as this is the same database I am asking about for help with a query: viewtopic.php?f=39&t=98155&p=471031

In case you have no time or interest in reading the whole post with the evolution of the database, below is a summary of my issue, starting with a brief outline of the tables involved:

I created a database to store information about fanfics (stories, often called fics) I had saved, and the main table is Fanfics, which stores the title and some other info about them. The relationship between fanfics and authors is many-to-many, so there is a junction table joining the Fanfics table with the Authors table called Fanfic_Author. (Currently this hasn't contributed to this question only because I only have a single fanfic entered that has more than one author, and only a couple test queries that would pull up that fanfic, and I haven't run those since the very beginnings of testing queries. Now I realize this is also a major issue, but I'll get to that later.)

The relationship between Fanfics and Fandoms is similar, with a Fandoms table and a Fanfic_Fandom junction table. Most fanfics only have one fandom (though those with more than one may prove to be an issue the same way the authors do), and my queries are often limited to entries assigned a specific fandom. In this case, I'm filtering by the fandom with FandomID value 19.

The relationship between Fanfics and Ships is similar to the other two, with a Ships table and a Fanfic_Ship junction table. While many fanfics have only one or even none assigned, others have two or even three or four. I ran across this issue because I first wanted to create a query pulling all of the fanfics in a specific fandom (19 being the FandomID) with a specific ship (ShipID being 49). This wasn't an issue when I wanted just any fics with that ship, and I could easily have left out the filtering by fandom first as only fanfics in that fandom would have that ship. (But you will see in a bit why I will need to filter by that fandom anyway.)

Below is that query:

Code: Select all

SELECT "Authors"."Author", "Fanfics"."Title", "Fanfics"."Rating", "Fanfics"."Wordcount", "Fanfics"."WebLink" FROM "Fanfic_Author", "Fanfic_Ships", "Fanfics", "Authors", "Fanfic_Fandom" WHERE "Fanfic_Author"."FanficID" = "Fanfics"."FanficID" AND "Fanfic_Author"."AuthorID" = "Authors"."AuthorID" AND "Fanfic_Fandom"."FanficID" = "Fanfics"."FanficID" AND "Fanfic_Fandom"."FandomID" = 19 AND "Fanfic_Ships"."FanficID" = "Fanfics"."FanficID" AND "Fanfic_Ships"."ShipID" = 49 ORDER BY UPPER ( "Authors"."Author" ) ASC, UPPER ( "Fanfics"."Title" ) ASC

As you can see, Rating, Wordcount, and WebLink are all columns in Fanfic that are providing info about each fanfic, and since every fanfic has at least one author (the database doesn't require it but I will never create a fanfic entry without assigning at least one), I can use the author as my first sorting criteria (which is useful for my target audience for some of these reports).

However, when I wanted all the fics in that fandom that did not have that ship, I ran into issues; if a fic had, say, ShipID 49, 50, and 51 assigned to it, excluding the 49 meant that I got both entries for 50 and 51 listed, meaning the fanfic was listed twice in my results! Adding DISTINCT collapsed the entries so they only appeared once - but I still have the issue that if ShipID 49 is assigned to that fanfic, I don't want any of the entries of that fic to appear in my query. That fanfic should be excluded entirely from my results. How do I do that?

I did search but got completely confused as to how to set up a join that would work with the junction tables properly. I mean, this is five different tables being joined, three of them being junction tables, with filtering IN using one table and excluding entries using another! More complicated than anything I was running across in search results. I looked at this link but it didn't seem to quite fit my issue, as I'm trying to exclude rows in one table based on criteria in a junction table, not whether it does or doesn't have data at all in some table/column: viewtopic.php?f=61&t=43188&p=199007

As far as the issue with authors and fandoms, if I were to run a query for the same ship but had fanfics with multiple authors assigned that had that ship, is there any way to combine the data (in a "Author1 & Author2" or some other format) so that all the data for that fanfic can be listed a single time in the query, rather than duplicated, once for each author? The same goes for any fic that is in more than one fandom. This seems to be a close relative to the issue above (and shares the same background info on the database setup), so I figured I'd list it as well. I've already experienced multiple entries for a single fanfic in one query I do because the fanfic has two fandoms assigned, but as I have few of those that meet the other criteria of that query (which is filtered by a column in the Fanfics table, making it immensely simpler than the one I'm asking about above), it hasn't been an issue so far - but I can foresee it becoming so as I continue to enter data (I'm only to 538 entries as of today and this will end up in the thousands eventually).

I welcome any help or suggestions you can provide.

If it matters, the database has been split to HSQLDB, and all queries are already running the SQL commands directly because I wanted case-insensitive alphabetization in report results and it wouldn't do that otherwise.

[Villeroy]

Simple approach with 2 helper tables: download/file.php?id=37196

This will also work with the not-equal operator.

Edit the query like this:

Code: Select all

SELECT "Data".* 
FROM "Criteria", "Data", "Filter" 
WHERE "Filter"."ID" = 0 
  AND (  "Data"."C" NOT IN (SELECT "C" FROM "Criteria" ) ) 
  OR ( "Filter"."B" = FALSE )

[chrisb]

if ship 49 has been assigned to a Fanfic then all entries for said Fanfic should be excluded from the result set?
one method is to use a sub-query. it's always difficult to code without access to db so hoping for the best.

Code: Select all

select
a."Author", 
f."Title", 
f."Rating", 
f."Wordcount", 
f."WebLink" 
from
"Fanfics" f
join
"Fanfic_Author" fa
on fa."FanficID"=f."FanficID"
join
"Fanfic_Fandom" ff
on ff."FanficID"=f."FanficID"
join
"Authors" a
on a."AuthorID"=fa."AuthorID"
left join --sub-query. enable identification of rows which contain the "FanficID" referenced by "ShipID" 49
(
	select "FanficID"
	from "Fanfic_Ships"
	where "ShipID" = 49
) fs
on fs."FanficID"=f."FanficID"
where
ff."FandomID" = 19
and 
fs."FanficID" is null --eliminate entries related to the "FanficID" of "ShipID" 49
order by upper("Author"), upper("Title")

[Doranwen]

it's always difficult to code without access to db so hoping for the best.

I apologize - I would provide the db itself but it has some private data in it at this point that would be very hard to strip away so I could share it. However, the empty version as is found in the linked post should have the same structure with empty tables and whatnot, just no data in it to work with; I've added a few columns to the Fanfics table and modified the form to allow for inputting data into those columns, but otherwise the rest of the table structure is identical.

I managed to follow your example completely, I believe, and came up with this:

Code: Select all

SELECT "Authors"."Author", "Fanfics"."Title", "Fanfics"."Rating", "Fanfics"."Wordcount", "Fanfics"."WebLink"
FROM "Fanfics"
JOIN "Fanfic_Author"
ON "Fanfic_Author"."FanficID" = "Fanfics"."FanficID"
JOIN "Fanfic_Fandom"
ON "Fanfic_Fandom"."FanficID" = "Fanfics"."FanficID"
JOIN "Authors"
ON "Fanfic_Author"."AuthorID" = "Authors"."AuthorID"
LEFT JOIN
( SELECT "Fanfic_Ships"."FanficID"
  FROM "Fanfic_Ships"
  WHERE "Fanfic_Ships"."ShipID" = 49
) "Fanfic_Ships"
ON "Fanfic_Ships"."FanficID" = "Fanfics"."FanficID"
WHERE "Fanfic_Fandom"."FandomID" = 19
AND
"Fanfic_Ships"."FanficID" IS NULL
ORDER BY UPPER ( "Authors"."Author" ) ASC, UPPER ( "Fanfics"."Title" ) ASC

And, indeed, it works!

If I want to exclude two different ShipIDs simultaneously (such as 49 and 56 both), how do I do that? (I assume I can exclude a ThemeID the same way - themes are also assigned 0-many to a fic, and have a many-to-many relationship with Fanfics that involves a junction table. Pretty sure I can follow this pattern to do that.) Or if I want all fics that include 50 that do not include 49? Those are two specific queries I had thought to create and didn't know how to do so.

[chrisb]

If I want to exclude two different ShipIDs simultaneously (such as 49 and 56 both)

replace the where clause in the left join of the original code with:
where "ShipID" in(49,56)

Or if I want all fics that include 50 that do not include 49?

got to change the code & alter the selection sequence a little here. not 100% sure!

Code: Select all

--used to include/exclude fantics related to specific ships
--as multiple ships can be assigned to a "FanficID" duplicate rows may be displayed hence the use of 'DISTINCT'
select distinct
a."Author",
f."Title",
f."Rating",
f."Wordcount",
f."WebLink"
from
(	
	select f1."FanficID" from
	(
		select "FanficID" --include all "FanficID"'s related to these ships
		from "Fanfic_Ships"
		where "ShipID" = 50			--single ship
--		where "ShipID" in(50,60)	--multiple ships
	) f1
	left join 
	(
		select "FanficID" --"FanficID"'s related to these ships will be excluded
		from "Fanfic_Ships"
		where "ShipID" = 49			--single ship
--		where "ShipID" in(49,56)	--multiple ships
	) f2
	on f1."FanficID"=f2."FanficID"
	where f2."FanficID" is null
) fs
join
	(select "FanficID" from "Fanfic_Fandom" where "FandomID" = 1) ff
	on ff."FanficID"=fs."FanficID"
join
"Fanfics" f
on f."FanficID"=ff."FanficID"
join
"Fanfic_Author" fa
on fa."FanficID"=f."FanficID"
join
"Authors" a
on a."AuthorID"=fa."AuthorID"
order by upper("Author"), upper("Title")

[Doranwen]

I am full of glee, because it works! I typed out all the table prefixes (or whatever you call them) because it helped me keep straight what was what:

Code: Select all

SELECT DISTINCT "Authors"."Author", "Fanfics"."Title", "Fanfics"."Rating", "Fanfics"."Wordcount", "Fanfics"."WebLink"
FROM
(
SELECT "Fanfics"."FanficID" FROM
	(
		SELECT "Fanfic_Ships"."FanficID"
		FROM "Fanfic_Ships"
		WHERE "ShipID" = 50
	)  "Fanfics"
	LEFT JOIN
	(
		SELECT "Fanfic_Ships"."FanficID"
		FROM "Fanfic_Ships"
		WHERE "Fanfic_Ships"."ShipID" = 49
	) "Fanfic_Ships"
	ON "Fanfics"."FanficID" = "Fanfic_Ships"."FanficID"
	WHERE "Fanfic_Ships"."FanficID" IS NULL
) "Fanfic_Ships"
JOIN
	(SELECT "Fanfic_Fandom"."FanficID" FROM "Fanfic_Fandom" WHERE "FandomID" = 19) "Fanfic_Fandom"
	ON "Fanfic_Fandom"."FanficID" = "Fanfic_Ships"."FanficID"
JOIN "Fanfics"
ON "Fanfics"."FanficID" = "Fanfic_Fandom"."FanficID"
JOIN "Fanfic_Author"
ON "Fanfic_Author"."FanficID" = "Fanfics"."FanficID"
JOIN "Authors"
ON "Fanfic_Author"."AuthorID" = "Authors"."AuthorID"
ORDER BY UPPER ( "Authors"."Author" ) ASC, UPPER ( "Fanfics"."Title" ) ASC

That is one complicated query, but it worked beautifully and gave me exactly the results I wanted. I was also able to tweak it to, for instance, include only fics with Ship 49 but exclude any that had Theme 41.

The last question I had regarding complicated queries was the combining authors or fandoms into one row, but that does seem to be a distinct query question from this, so I'll post it separately.

I think I should be able to tweak the patterns you gave to adapt to whatever combination of filtering I'm going for from now on, and it makes sense as I look at the SQL (I just wouldn't have figured out how to compose it myself). Thank you so much! This is marvelous.