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 have the following tables and fields:
Words: WordID [primary key], Word [varchar], and other fields irrelevant to this query
Words_Meta [junction table]: WordID / MetaID [compound primary key]
There's a Meta table itself but I actually don't need any fields from that, as the only field I need visible in the query results is the Word field (the junction table is being joined because I want to filter which records in Words are returned based on whether there are entries in the junction table for that word that contain and/or exclude specific MetaID values). It doesn't factor into any of my queries.
I was able to use the count query I was shown here to figure out how to use a left join without the count to exclude one or more values of MetaID from the junction table and list the individual records that resulted. What I can't figure out how to do is to simultaneously *restrict* my query to one or more values. (Even more complicated, I suspect, is how to restrict it to value 1, OR 2, OR 3. There is a situation where I could see wanting any record in Words that has at least one of two possible MetaID values in that junction table, for instance.) I can't quite figure out which join to go about all of it, and I think it's that top-level understanding I'm still missing, what the overall structure of the query should be for that, and it's not sinking in from all my reading, even if I know what a left join does vs. inner join, etc.
I'm currently excluding 1, 3, 4, 5, 6, 7, 11, 21, and I'm wanting to restrict to only those which have 2 in it (and from there I suspect I can easily switch it to excluding that list minus the 1, and restrict to only those with 1 and 2 - for the OR, I'd test with 1 or 2 also, or 1, 2, or 3).
And if I want to left join a second table (say, Words_Vowels) the same way as the first (with that one I'd *probably* only exclude a series of values, not restrict to specific ones as well), what do I need to know re: piecing it together? It's easy enough to switch out the names, but the "IS NULL" part confuses me (why do we specify where that ID is null?) and I'm not sure how to group it so the syntax works. I tried that and failed miserably, lol.
EDIT: A friend helped me with the filtering and this seems to work fine:
Code: Select all
SELECT "Words"."Word"
FROM "Words"
LEFT JOIN
( SELECT "Words_Meta"."WordID"
FROM "Words_Meta"
WHERE "Words_Meta"."MetaID" IN (1, 3, 4, 5, 6, 7, 11, 21)
) wm
ON wm."WordID" = "Words"."WordID"
WHERE "WordID" IN (SELECT "Words_Meta"."WordID" FROM "Words_Meta" WHERE "MetaID" = 2)
AND "Words"."Mapping" = 1
AND "Words"."Name" = 0
AND "Words"."Place" = 0
AND wm."WordID" IS NULL
ORDER BY UPPER ("Words"."Word") ASC
EDIT AGAIN! So I figured out multiple left joins. FInally my brain figured out that I was putting the WHERE statement in the wrong place - they have to always be at the end, even with two left joins back-to-back. ON goes with the left joins, WHERE waits for the end.
So I guess I'm down to just the OR question - can I restrict to records where MetaID is either 1 *or* 2 (but not necessarily both)? Or 1, 2, or 3 as possibilities?