[Solved] Full join with complicated queries?

Doranwen · Post by **Doranwen** » Tue Jun 14, 2022 6:20 pm

I'd happily take the ability to reference one query in another, but I think that would also be fairly complicated, if it were even possible.

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

Below here is the *next* lesson's wordlist only, words that are added with that list:

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

Normally a lesson's wordlist query only needs to focus on a single id being present in all words. In this case, however, I had to narrow it down with two possibilities, or it would pull in a bunch of words that were not part of that lesson.

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.

edit this topic's initial post · Post by **Villeroy** » Tue Jun 14, 2022 6:34 pm

which database are we discussing here? hsql? https://forum.openoffice.org/en/forum/v ... hp?t=96654

Mountaineer · Post by **Mountaineer** » Tue Jun 14, 2022 8:05 pm

Doranwen wrote: ↑Tue Jun 14, 2022 6:20 pm ..., which is a split database with HSQLDB 2.5.1 as the backend, LibreOffice Base as the frontend.

so we have hsql 2.5

..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. ...

If you edit your query in SQL-Mode, activate direct SQL (right-most icon in the toolbar in my setup), so Base will not interfere with your query.

Doranwen · Post by **Doranwen** » Tue Jun 14, 2022 10:08 pm

Villeroy wrote: ↑Tue Jun 14, 2022 6:34 pm which database are we discussing here? hsql? https://forum.openoffice.org/en/forum/v ... hp?t=96654

My second paragraph explains that it's a "split database with HSQLDB 2.5.1 as the backend, LibreOffice Base as the frontend". I looked at the sample odb there but it didn't help me figure out how to merge these two highly complicated queries.

Mountaineer wrote: ↑Tue Jun 14, 2022 8:05 pm If you edit your query in SQL-Mode, activate direct SQL (right-most icon in the toolbar in my setup), so Base will not interfere with your query.

It already is (pretty much all my queries but the most basic use things that won't work without direct SQL mode), so does that mean the full join *will* work?

If so, I'm back to the problem of the complicatedness making my head ache trying to figure out how to join them. I can somewhat follow how *one* of these queries works but doing anything more than tweaking which IDs are excluded or specifying a few conditions under WHERE starts to overwhelm. Trying to figure out how combine both of them into a single query…

I did attempt it (using "full join" in case that actually worked), but I got an error message that basically dumped the entire query back at me and said the user lacked privilege or object wasn't found (and it had nothing to do with the full join), even though I introduced it the same way the others were in the query (and yes, direct SQL mode is definitely on), so either nested stuff doesn't work like I thought or I really have gotten confused.

This was my attempt (using abbreviations for "lesson" and "cumulative" to mark the two different queries):

Code: Select all

SELECT "Words"."Word", "Words"."Frequency"
FROM "Words"
RIGHT JOIN
	(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) les
ON les."WordID" = "Words"."WordID"
FULL JOIN
	(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) cum
ON cum."WordID" = "Words"."WordID"
ORDER BY UPPER ("Words"."Word") ASC

Obviously there's something about joins that I don't quite understand how to set up. I'm sure I've gotten it way off and am probably complicating it worse, but I'm not getting anywhere staring at it myself. (I tried right join, regular join, etc. at the beginning, but there's a bigger issue than which join to use. If I had the rest of the syntax right I could sort that out just fine based on the results I got from each.) I know I had a challenge just getting the base query (which spawned the two I'm trying to combine) set up here: https://forum.openoffice.org/en/forum/v ... 74#p505274

chrisb · Post by **chrisb** » Wed Jun 15, 2022 1:48 am

words selected in query 2 may not selected in query 1 therefore joining the queries will not be possible.
the second query in particular is a little confusing, it may require a clean up.
give this code a go & if it returns results equivalent to your second query then we can solve your issue by using the UNION operator.

Code: Select all

select w."Word", w."Frequency"
from
	(select "WordID", "Word", "Frequency" from "Words" where "Edited" = 1 and "Name" = 0 and "Place" = 0) w
join
	(SELECT "WordID" FROM "Words_Consonants" WHERE "ConsonantID" = 34) wc
	on w."WordID" = wc."WordID"
join
	( SELECT "WordID" FROM "Words_Meta" WHERE "MetaID" = 0 ) wm
	on wm."WordID" = wc."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" = w."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" = w."WordID"
where wv."WordID" IS NULL AND wb."WordID" IS NULL

Doranwen · Post by **Doranwen** » Wed Jun 15, 2022 2:16 am

Unfortunately, it does not. :/ The original second query specifically rules out any words that have a MetaID in the included list - including those with MetaID = 21. This query permits those and pulls in a bunch of words that don't belong in that lesson. (There are also id #s from Consonants and Vowels that should be excluded but which are in the list, as well as other Meta ids.)

The trouble with these queries is that they have to start from a place of *exclusion* - only allowing words which contain the specified elements (but which do not necessarily contain all of those elements) - and as I advance in the lessons, I remove the IDs of elements that are now included. Specific lesson queries have to pull *just* the words which contain specific elements, but which do not have any elements not taught yet. Cumulative lesson queries are a little simpler as they just have to include any word that can be composed from the elements already taught.

It's like the game of "how many words can you make from these letters?" - you don't have to use all the letters but you don't get to use any letters not in that list. This query's giving me any word that has the specific elements taught in that lesson but not limiting it to the list of elements already taught, so it's got elements from Meta, Consonants, and Vowels (would have Combo too but those aren't as common and I don't think any of those were in the words which have the required elements) which are not taught yet and shouldn't be in the list.

chrisb · Post by **chrisb** » Wed Jun 15, 2022 3:23 am

have used UNION ALL to merge your queries. LEVEL = 1 = first query, LEVEL = 2 = second query.
hope it works?

Code: Select all

select * from
(
	select level, "Words"."Word", "Words"."Frequency"
	from
		(SELECT 1 level, "Words".* FROM "Words") "Words"
	LEFT JOIN
	(
		SELECT 1 level, "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

	union all

	select 2, "Words"."Word", "Words"."Frequency"
	from
		(SELECT "Words".* FROM "Words") "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 ("Word")

Doranwen · Post by **Doranwen** » Wed Jun 15, 2022 4:28 am

Yay, it does! Thank you! I will have to study this to see if I can understand what you did and how to do it myself. I generally learn more from that than from the barebones examples of SQL commands (which are never anywhere as complex as my databases are, lol).

[Solved] Full join with complicated queries?

[Solved] Full join with complicated queries?

Re: Full join with complicated queries?

Re: Full join with complicated queries?

Re: Full join with complicated queries?

Re: Full join with complicated queries?

Re: Full join with complicated queries?

Re: Full join with complicated queries?

Re: Full join with complicated queries?