[Solved] Restrict query results based on OR possibilities

[Doranwen]

I keep trying to read SQL examples, model after various queries I've been given, and try any combination of the two, but somehow I miss the general understanding of what is going on in a query and WHY I'm doing what I'm doing in order to get the answers. I can follow where I am in a query, but knowing why I need this line or that one, I can't always say that, and I suspect that's why I'm failing to construct the one I've been trying to do this evening, despite multiple attempts.

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

I'm pretty sure I can do the IN thing to restrict by multiple MetaIDs at once. What I'd like to still figure out is, is it possible to restrict on an OR basis - as in, if the record has MetaID 1 *or* MetaID 2, can words fitting *either* one (but not necessarily both) be pulled? And the syntax for multiple left joins still escapes me (I'll need to join Words_Consonants, Words_Vowels, and Words_Combo and do exclusions on all of those, so that's up to four different left joins at once).

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?

[chrisb]

can I restrict to records where MetaID is either 1 *or* 2?

you need to be more specific, i am confused!
your code restricts "MetaID" to the single value of '2' it also excludes multiple values including '1'.
to restrict multiple values you would replace EQUALS with IN i.e. WHERE "MetaID" = 2 with WHERE "MetaID" in(2,8).

you may be saying that you wish to select a "WordID" with a "MetaID" of '2' which also has a "MetaID" of '1'.
please clarify.

[Doranwen]

Lol, sorry, I solved my own problem last night and forgot to come back and post it. I think I'm finally starting to understand how to filter my results the way I want!

So here are the successful queries, in case anyone else needs to see the contrast.

Restricting results to those with MetaID 1 *and* 2 both:

Code: Select all

SELECT "Words"."Word"
FROM "Words"
LEFT JOIN
	(
	SELECT "Words_Meta"."WordID"
	FROM "Words_Meta"
	WHERE "Words_Meta"."MetaID"
	IN ( 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" = 1 )
AND "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

versus

Restricting results to those with MetaID 1 *or* 2:

Code: Select all

SELECT "Words"."Word"
FROM "Words"
LEFT JOIN
	(
	SELECT "Words_Meta"."WordID"
	FROM "Words_Meta"
	WHERE "Words_Meta"."MetaID"
	IN ( 4, 5, 6, 7, 11, 21 )
	) "wm"
ON "wm"."WordID" = "Words"."WordID"
WHERE "WordID" IN ( SELECT "Words_Meta"."WordID" FROM "Words_Meta" WHERE "MetaID" IN ( 1, 2 ) )
AND "Words"."Mapping" = 1
AND "Words"."Name" = 0
AND "Words"."Place" = 0
AND "wm"."WordID" IS NULL
ORDER BY UPPER ( "Words"."Word" ) ASC

Thank you for trying to help with my lack of clarity, and a big thanks to everyone here who's helped me all along, as I can definitely feel my general understanding of SQL increasing all the time.

[eremmel]

Hi Doranwen,

I just saw your post. A quite complicated query. Here is another suggestion, not sure if this works with your database (setup). I takes all the filter steps in one shot. The idea is that you like to see those WordIDs from Words_Meta that satisfy your conditions: 1 and/or 2 AND NOT (3, 4, ...).
You can do this by counting the (1,2)s and the (3,4,..)s via a SUM. The results of those SUMs should be (1 or 2) and 0 resp.

Here the query:

Code: Select all

SELECT "Words"."Word"
FROM "Words"
INNER JOIN (
   SELECT "WordID"
   FROM "Words_Meta"
   GROUP BY "WordID"
   HAVING SUM( CASE WHEN "MetaID" <= 2 THEN 1 ELSE 0 END) = 2   -- take 1 for OR condition
            AND SUM( CASE WHEN "MetaID" IN ( 3, 4, 5, 6, 7, 11, 21 ) THEN 1 ELSE 0 END) = 0
) "wm" ON "wm"."WordID" = "Words"."WordID"
WHERE "Words"."Mapping" = 1
    AND "Words"."Name" = 0
    AND "Words"."Place" = 0
ORDER BY UPPER ( "Words"."Word" ) ASC

The inner-query can even a bit smarter with

Code: Select all

   SELECT "WordID"
   FROM "Words_Meta"
   GROUP BY "WordID"
   HAVING SUM( CASE WHEN "MetaID" <= 2 THEN 1 
                                      WHEN "MetaID" IN ( 3, 4, 5, 6, 7, 11, 21 ) THEN 10 
                                      ELSE 0 END) = 2 -- take 1 for OR condition

Have fun....
P.S. This approach become more easy to your mind when you think in data-sets (in this case two) Your approach is to start thinking from Words table and then filter (out) from Words_Meta step by step.