"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,
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",
			min("Priority") min_priority
		from
			"tDrama-Genre"
		where
			"Genre-ID" in(21,22)
		group by
			"Drama-ID"
	) b
		on a.ID = b."Drama-ID"
	where
		b.min_priority is null
		or
		b.min_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"

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