[Solved] Count query with junction tables, conditions, sort

[Doranwen]

I seem to have a knack for needing the most complicated queries, and everything I search only shows me how to do much more simple ones - and I really cannot figure out from the simple ones how to get to the complicated ones I need, lol.

I'm working in LO with the database that was being formed here: viewtopic.php?f=39&t=103544
I've added a few more fields to the Words table, and edited the fields in some of the other tables, but the general structure is the same. (I split it ages ago and added tons of records to it, so there's absolutely no way to attach the current version anywhere for anyone to see.)

I'll need to create two or three queries but they will be nearly-identical to each other, so it shouldn't be too tricky to adapt once I have one of them, so I'm going to describe one.

I have the following tables and fields:

Words: WordID [primary key], Word [varchar], Mapping [tinyint - assigned 0 by default, and 1-4 by means of radio buttons], and other fields irrelevant to this query
Words_Vowels [junction table]: WordID / VowelID [compound primary key]
Vowels: VowelID [primary key], Vowel [varchar]

What I want to do is to create a query involving a count so that it'll list each Vowel from Vowels with a count of how many times it appears in Words_Vowels, with the condition that it only count VowelIDs for records in Words that match a particular Mapping number, say 1. If the Mapping field has 0, 2, 3, or 4 (the only other possibilities), the query needs to ignore all records with that WordID in the junction table. I would also like it to sort the results of that query in descending order numerically so that the vowel with the greatest number of appearances is at the top, and the one that appears the least at the bottom.

And if THAT weren't complicated enough, I realize that I have another condition - to exclude all records in the count for which there are entries in yet ANOTHER junction table (Words_Meta) with a particular MetaID - 21, in this case. (Words_Meta has a compound primary key of WordID and MetaID.) At some point I'll want to run versions of the query that do not have that condition, so I need to figure out both the query described above (with only the Mapping condition) and the query just described (with the MetaID condition in addition to the Mapping condition).

I found websites and pages describing the COUNT function (with some fairly clear examples for very simple situations), and I found even one or two that demonstrated using COUNT on a junction table, but they all had weird alias stuff going on (none of them was for OO or LO, and I know there are differences between the SQL that can be run with Base and what can be run in some other SQL-handling programs), and I couldn't follow the examples properly enough to compose a query for my own database. I know this is quite complicated but am hoping someone more experienced can see how to do this.

[chrisb]

hello Doranwen,
2 bits of code which are almost identical, one with & the other without Meta condition.
i have used single values for both "Mapping" & "MetaID" as per spec.

you probably only require the code with Meta Condition.
setting the literal value of "MetaID" to say -1 will return a result set equivalent to that returned by without Meta Condition.

hope it works?

Code: Select all

--with Meta Condition
select
	v."VowelID", v."Vowel", count(v."Vowel") "Count"
from
(
	select w."WordID"
	from
		(select "WordID" from "Words" where "Mapping" = 1) w --literal value for "Mapping"
		left join
			(select "WordID" from "Words_Meta" where "MetaID" = 21) wm --literal value for "MetaID"
			on wm."WordID" = w."WordID"
	where wm."WordID" is null
) w

left join
	"Words_Vowels" wv
	on wv."WordID" = w."WordID"

left join
	"Vowels" v
	on v."VowelID" = wv."VowelID"

group by v."VowelID", v."Vowel"
order by "Count" desc, v."Vowel"

Code: Select all

--without Meta Condition
select
	v."VowelID", v."Vowel", count(v."Vowel") "Count"
from
	(select "WordID" from "Words" where "Mapping" = 1) w --literal value for "Mapping"

left join
	"Words_Vowels" wv
	on wv."WordID" = w."WordID"

left join
	"Vowels" v
	on v."VowelID" = wv."VowelID"

group by v."VowelID", v."Vowel"
order by "Count" desc, v."Vowel"

[Doranwen]

Ooh, yes, this does indeed work! I tested both of them and it was lovely.

What is the lowercase letter stuff in there? Is it some shorthand for the tables? It appears that way but I don't know what that's called or how it can be used. I think I need to understand that so I can adapt these to the other tables I'd like to run the similar queries on (the Consonants one, for instance).

How would I exclude multiple MetaID numbers if I wanted? 1, 2, and 3 are all ones I would like to be able to exclude in some initial queries, now that I look at the list again.

[Villeroy]

The letters are arbitrary alias names for the SELECT statements in braces.
Simplified example:

Code: Select all

SELECT a.X, b.X
FROM (SELECT X FROM TableA) AS a
    JOIN (SELECT X FROM TableB) AS b ON a.ID=b.ID

this way you specify which column comes from which record set, the first SELECT is a, the second SELECT is b. You may also use talking names.

[Doranwen]

Ah, by putting the alias names after the table names, it assigns them?

Like here:

Code: Select all

(select "WordID" from "Words" where "Mapping" = 1) w

and here:

Code: Select all

"Words_Vowels" wv

and here:

Code: Select all

"Vowels" v

[Villeroy]

Yes. You may insert the keyword AS for clarification but that is optional.
SELECT w.column FROM (SELECT X AS column FROM "Table") AS w

or when a database suffers from stupid table names:
SELECT w.X AS column FROM "Too_Long_Table_Name" AS w
In complex queries it is easier to use a one-letter alias instead of full names of tables and columns. In other cases you may prefer talking names for better understanding.

[SQL] JOINing 2 row sets

[chrisb]

What is the lowercase letter stuff in there?

as Villeroy has stated these letters are table aliases.
its a matter of preference but i always use lower case although internally HSQLDB converts them to uppercase.
if two different tables contain identically named fields then it's essential to be explicit or the db will throw an ambiguity error.

the tables "Vowels" & "Words_Vowels" both contain a field called "VowelID" which means that we must reference them explicitly.
we could reference them as "Vowels"."VowelID" & "Words_Vowels"."VowelID" but it's cleaner & quicker to use v."VowelID" or wv."VowelID".
FROM "Words_Vowels" wv is the equivalent of FROM "Words_Vowels" AS wv, the AS keyword is optional.

How would I exclude multiple MetaID numbers?

use the IN operator like so:

Code: Select all

(select "WordID" from "Words_Meta" where "MetaID" in(1, 2, 3)) wm --literal values for "MetaID"

Edit: 2021 Jan 15 added missing parenthesis to end of above code

[Doranwen]

Thank you! I feel like I've learned quite a bit here.