Record not being selected in a query

[UncleRobin]

In my small DB, I have the following query as a view

SELECT DISTINCT "tDramas"."TITLE", "tCountries"."COUNTRY", "tSTATUS"."Status", "tDramas"."Times Watched", "tDramas"."SCORE" FROM "tDramas", "tLanguages", "tSTATUS", "tCountries", "tNetworks", "tTYPE" WHERE "tDramas"."LANGUAGE" = "tLanguages"."Lang-ID" AND "tDramas"."STATUS" = "tSTATUS"."Status-ID" AND "tDramas"."COUNTRY" = "tCountries"."Country-ID" AND "tDramas"."NETWORK" = "tNetworks"."Network-ID" AND "tNetworks"."Country" = "tCountries"."Country-ID" AND "tDramas"."TYPE" = "tTYPE"."Type-ID" AND "tDramas"."Eps Watched" >= 1 ORDER BY "tDramas"."SCORE" DESC

But a few hours ago I noticed that one record is not being selected.

And here’s what I see in the form I use for entering Drama info into the table

The query is supposed to show me every Drama that I’ve watched at least 1 epsiode of. When I compare the query results to doing a manual filter of the main table removing Dramas with an “Eps watched” count of zero, there is a difference of exactly one: The query returns 760 records, filtering out the zero “eps watched” Dramas manually gives 761.

I cannot figure out why this one record, and only this one record is not being slected by the query. What am I doing wrong here?

I have started work on a completely new version of this DB, so finding out what mistake I’ve made here will mean one less bug in the new one. I’m attaching the current DB as well
AsianEntDates.odb (636.7 KB)

[RobertG]

Don’t know what you want to get, but the country of Network and Drama aren’t the same for some rows:
Remove
AND "tNetworks"."Country" = "tCountries"."Country-ID"
and you will get all rows you want. “tNetworks”.“Country” for the shown row is '1' but the Country-ID in tDramas is '5'.

[cpb]

@UncleRobin,
design your queries in SQL View.
use JOIN rather than WHERE as advised by Fred Toussi the HSQL developer.
no need for DISTINCT.
this SQL is your SQL with the WHERE clauses replaced by JOIN clauses, it’s coded and processed in exactly the same order.
requires direct mode.
if it’s to be used as data-source for form or report then you may need to create a view.

select 
	d.TITLE, 
	c.COUNTRY, 
	s."Status", 
	d."Times Watched", 
	d.SCORE 
from 
	"tDramas" d
join
	"tLanguages" l on d."LANGUAGE" = l."Lang-ID"
join
	"tSTATUS" s on d."STATUS" = s."Status-ID"
join
	"tCountries" c on d."COUNTRY" = c."Country-ID"
join
	"tNetworks" n on d."NETWORK" = n."Network-ID"
join
	"tTYPE" t on d."TYPE" = t."Type-ID"
where 
	d."Eps Watched" >= 1 
order by
	d."SCORE" DESC

[UncleRobin]

THank you! Your reply highlighted the problem - The Drama was from Taiwan but aired on a network from China. I’ll be able to build that situation into the next iteration of the DB

[UncleRobin]

This was great, thank you! Designing queries in SQL is one of many improvements on the todo list for the new version of my DB, your supplying this improtant query is very much appreciated.