"Greater than" selection query not working as intended

[UncleRobin]

In my hobby DB, I made a query the other night to look for Dramas where two genres were either not present at all, or had a lower priority, excluding all Dramas from a certain country. This is the SQL generated by the Base GUI

SELECT "tdramas"."title",
       "tcountries"."country",
       "tdramas"."score",
       "tstatus"."status",
       "tgenre"."genre",
       "tdrama-genre"."priority"
FROM   "tdramas",
       "tcountries",
       "tstatus",
       "tdrama-genre",
       "tgenre"
WHERE  "tdramas"."country" = "tcountries"."country-id"
       AND "tdramas"."status" = "tstatus"."status-id"
       AND "tdrama-genre"."drama-id" = "tdramas"."id"
       AND "tdrama-genre"."genre-id" = "tgenre"."id"
       AND ( "tdramas"."score" > 8
             AND "tstatus"."status" = 'completed'
             AND "tdrama-genre"."priority" > 2
             AND "tcountries"."country-id" <> 4
             AND ( "tdrama-genre"."genre-id" = 21
                    OR "tdrama-genre"."genre-id" = 22 )
              OR "tdramas"."score" > 8
                 AND "tstatus"."status" = 'completed'
                 AND "tcountries"."country-id" <> 4
                 AND "tdrama-genre"."genre-id" <> 21
              OR "tdramas"."score" > 8
                 AND "tstatus"."status" = 'completed'
                 AND "tcountries"."country-id" <> 4
                 AND "tdrama-genre"."genre-id" <> 22 )
ORDER  BY "tdramas"."score" DESC 

It works reasonably well, as long as the Dramas have more than 2 genres associate with them. So if genre 21 or 22 is priority 3 or 4, then they show up properly. If, though, the Dramas has fewer than 3 genres associated with, then Dramas with 21 or 22 listed in priority 1 or 2 still show up. As the two genres inquestion are OFTEN the only 2 listed, this means the query’s usefulness is suboptimal. How do I exclude those Dramas with fewer than 3 Dramas where the selected genres appear as either priority 1 or 2?

After running tyhe query, I went to the Base manual and spent quite a bit of time reading the sectionon queries trying to figure out the syntax I need, but it stumped me. THis query is one that will be useful in the next interation of my DB (espcially once I add parameters for choosing which genres to exclude, something I do know how to do) , so I’d be very grateful for help understanding what’s needed to achieve
“Include all Dramas from all countries except X where genres Y & Z are not present at all or are listed with priorities >2” including those Dramas with only 1 or 2 listed genre priorities.

[skyandrews]

May I suggest splitting this query into 3 or 4 seperate serial queries, each handling only a smaller, distinctive part of the full query. Debug each of the smaller queries (easier) and when completed, rejoin the sql’s back into one main query. Just by looking at the main sql does not reveal essential details such as data type mismatches, table relationships, etc… A stripped down sample of the odb would be very helpful.

[UncleRobin]

Thank yu for the serial query suggestion, I can see that make a lot of sense. I’m attaching a stripped down version of the odb as requested, only the tables and this one query left.
AsianEntTest.odb (234.6 KB)

[cpb]

@UncleRobin,
2nd EDIT 2025-04-30 09:25:
I missed the last 2 lines of your initial post.
have updated SQL in the LEFT JOIN in correspondence.
.
this is a little ambiguous: or had a lower priority
.
it looks like we need to select all records where
"Genre-ID" in(21, 22) and “Priority” <= 2
or
a drama does not have a “Genre-ID” of 21 or 22
.
you may be confusing yourself by using ‘AND “tdrama-genre”.“priority” > 2’.
.
keep in mind that it’s possible for a drama have both “Genre-ID” 21 and 22.
.
based on my assumptions I came up with this (requires direct mode):

select
	d.ID, d.TITLE, c.COUNTRY, d.SCORE, d.STATUS, g.GENRE, dg."Priority"
from
(
	select
		a.*
	from
	(
		select
			ID, TITLE, COUNTRY, SCORE, STATUS
		from
			"tDramas"
		where
			SCORE > 8
			and
			COUNTRY <> 4
	) a
	left join
	(
		select
			"Drama-ID",
			max("Priority") max_priority
		from
			"tDrama-Genre"
		where
			"Genre-ID" in(21,22)
		group by
			"Drama-ID"
	) b
		on a.ID = b."Drama-ID"
	where
		b.max_priority is null
		or
		b.max_priority > 2
) d
join
	"tCountries" c on d.COUNTRY = c."Country-ID"
join
	"tDrama-Genre" dg on dg."Drama-ID" = d.ID
join
	"tGenre" g on dg."Genre-ID" = g.ID
order by
	SCORE desc,
	TITLE,
	"Priority"

[UncleRobin]

Thank you both very muxh for your help with this. The SQl sample supplied seems to be pretty much exactly what I was looking for.

My apologies if my earlier posts lacked clarity. Because my personal preferences for East Asian Dramas tend to lean toward those focused on romance, I have two separate genres (21 and 22) for “romance” and “romcom”.
Many Dramas do have both, and in that case, they may be the only two genres included in my records. Some only have one or the other, and in some case either or both of those elements may be present but not major features. That lower prioritymeans a numerically higher value, hence my “>2”.

The purpose of the query was to look for Dramas that I had scored at least 8/10 from countries other than South Korea (4) which either did not have genres 21 or 22 present at all, or had (either one of) them at a lower priority.

The issue I was having, which the SQL sample so kindly provided has addressed, was that those Dramas (of which there were many) which had either only genre 21 or 22, or only both, were being included in the results, despite the priorities being <2.

I will be studying the supplied query to make sure I understand how and why it works, as it’s going to be very useful in the next iteration of my database.

Again, many thanks to you both for your patience and assistance.

[skyandrews]

AsianEntTweak1.odb (235.4 KB)
Here is a tweaked version of your sample that has three serial queries. Really difficult to know for what, exactly you are looking, but this should point you in the right direction.