I'm working on the sound-symbol database I'd developed about a year and a half ago, which is a split database with HSQLDB 2.5.1 as the backend, LibreOffice Base as the frontend. It has five main tables: Words, Meta, Consonants, Vowels, and Combo, as well as junction tables between Words and the other four (Words_Meta, Words_Consonants, etc.), each of which uses a joint primary key with the WordID and the id for the specific table (MetaID, ConsonantID, etc.). Each record in Words is, obviously, a word. As I edit each word, I assign sound-symbol correspondences by selecting the ones that match the word from each of the four tables.
In setting up queries for a series of reading lessons, I'm setting up a query to *exclude* all words except for those which are composed of a limited set of sound-symbol correspondences, specific collections of ID numbers from the various tables.
Below is the cumulative word list for a specific lesson (all words up to and including the ones possible with that lesson):
Code: Select all
SELECT "Words"."Word", "Words"."Frequency"
FROM "Words"
LEFT JOIN
( SELECT "Words_Meta"."WordID"
FROM "Words_Meta"
WHERE "Words_Meta"."MetaID" IN (0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 13, 21)
) wm
ON wm."WordID" = "Words"."WordID"
LEFT JOIN
( SELECT "Words_Consonants"."WordID"
FROM "Words_Consonants"
WHERE "Words_Consonants"."ConsonantID" IN (0, 3, 4, 6, 7, 8, 10, 11, 12, 13, 14, 15, 16, 18, 19, 20, 21, 22, 25, 26, 28, 29, 32, 33, 35, 36, 37, 38, 39, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66)
) wc
ON wc."WordID" = "Words"."WordID"
LEFT JOIN
( SELECT "Words_Vowels"."WordID"
FROM "Words_Vowels"
WHERE "Words_Vowels"."VowelID" IN (1, 3, 4, 5, 7, 8, 9, 10, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110, 111, 112, 113, 114, 115, 116, 117, 118, 119, 120, 121, 122, 123)
) wv
ON wv."WordID" = "Words"."WordID"
LEFT JOIN
( SELECT "Words_Combo"."WordID"
FROM "Words_Combo"
WHERE "Words_Combo"."ComboID" IN (0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64)
) wb
ON wb."WordID" = "Words"."WordID"
WHERE wm."WordID" IS NULL
AND wc."WordID" IS NULL
AND wv."WordID" IS NULL
AND wb."WordID" IS NULL
AND "Words"."Edited" = 1
AND "Words"."Place" = 0
ORDER BY UPPER ("Words"."Word") ASC
Code: Select all
SELECT "Words"."Word", "Words"."Frequency"
FROM "Words"
LEFT JOIN
( SELECT "Words_Meta"."WordID"
FROM "Words_Meta"
WHERE "Words_Meta"."MetaID" IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 13, 21)
) wm
ON wm."WordID" = "Words"."WordID"
LEFT JOIN
( SELECT "Words_Consonants"."WordID"
FROM "Words_Consonants"
WHERE "Words_Consonants"."ConsonantID" IN (0, 3, 4, 6, 7, 8, 10, 11, 12, 13, 14, 15, 16, 18, 19, 20, 21, 22, 25, 26, 28, 29, 32, 33, 35, 36, 37, 38, 39, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66)
) wc
ON wc."WordID" = "Words"."WordID"
LEFT JOIN
( SELECT "Words_Vowels"."WordID"
FROM "Words_Vowels"
WHERE "Words_Vowels"."VowelID" IN (1, 3, 4, 5, 7, 8, 9, 10, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110, 111, 112, 113, 114, 115, 116, 117, 118, 119, 120, 121, 122, 123)
) wv
ON wv."WordID" = "Words"."WordID"
LEFT JOIN
( SELECT "Words_Combo"."WordID"
FROM "Words_Combo"
WHERE "Words_Combo"."ComboID" IN (0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64)
) wb
ON wb."WordID" = "Words"."WordID"
WHERE "WordID" IN ( SELECT "Words_Consonants"."WordID" FROM "Words_Consonants" WHERE "ConsonantID" IN (34) )
AND "WordID" IN ( SELECT "Words_Meta"."WordID" FROM "Words_Meta" WHERE "MetaID" = 0 )
AND wm."WordID" IS NULL
AND wc."WordID" IS NULL
AND wv."WordID" IS NULL
AND wb."WordID" IS NULL
AND "Words"."Edited" = 1
AND "Words"."Name" = 0
AND "Words"."Place" = 0
ORDER BY UPPER ("Words"."Word") ASC
I find myself stumped when it comes to the cumulative wordlist for this lesson, though. I need it to include all words from the *previous* cumulative list (as generated from the previous query above), and add *just* the words from this list. If I simply remove both the two IDs above from the list of excluded IDs, though, I'll get the whole bunch of words that are not part of the new lesson (and for which the new lesson's query had to restrict on two different id numbers to avoid). And I'll need to do this for several lessons in a row until that extra batch of words is included in a later lesson.
I need a way to say "anything from the first query plus anything from the second query, combine into one list" - but both queries are so complicated that I can't even begin to figure out how to combine them. The SQL join options make it clear that what is needed is a full (outer) join, except I'm not sure Base can even handle those, nor can I figure out how to set that up. The workaround suggested in at least one place on this forum (involving left and right joins and a union) is even more complicated.