Trying to understand SQL "OR" syntax problem

UncleRobin · October 6, 2021, 10:55pm

I tried running a query last night to find all the Dramas I’ve completed with a score >8.5 and whose Genre was either Romance (ID 21) or Romcom (ID 22). This was the SQL generated by building the query in Design View

SELECT "tDramas"."TITLE", "tCountries"."COUNTRY", "tDramas"."SCORE", "tSTATUS"."Status", "tGenre"."GENRE", "tDrama-Genre"."Drama-ID" 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.75 AND "tSTATUS"."Status" = 'completed' AND "tDrama-Genre"."Genre-ID" = 21 OR "tDrama-Genre"."Genre-ID" = 22 )

And this was the output:

As shown, it is selecting the 21 or 22, but is ignoring the >8.5 criterion. In Design View, it looks like this:

The reason I posted the Design View shot was because looking at the Design View I wondered what would happen if tried removing one of the two “OR” criteria. The result was this:

 SELECT "tDramas"."TITLE", "tCountries"."COUNTRY", "tDramas"."SCORE", "tSTATUS"."Status", "tGenre"."GENRE", "tDrama-Genre"."Drama-ID", "tDrama-Genre"."Genre-ID" 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.75 AND "tSTATUS"."Status" = 'completed' AND "tDrama-Genre"."Genre-ID" = 21

When I looked at the SQL for that query, I thought “I wonder what would happen if I simply type OR 22 at the end of that SQL?” What happened is that LO completely hung and I had to open Task Manager and End Task.

Since I have the information I was after, the workaround worked, more or less. But I am curious to know why the “21 or 22” didn’t work, and why the syntax of the query with the “OR” is so different from those without.

Ratslinger · October 6, 2021, 11:52pm

Hello,

A close look at you results will help. When you eliminated "Genre-ID" = 22 notice that those results were among those in the first list where you had too many items in the result. Now outside of the 21's, it appears ALL 22's are there. This is a result of your OR condition not being grouped correctly. Your statement:

SELECT "tDramas"."TITLE",
       "tCountries"."COUNTRY",
       "tDramas"."SCORE",
       "tSTATUS"."Status",
       "tGenre"."GENRE",
       "tDrama-Genre"."Drama-ID"
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.75 AND "tSTATUS"."Status" = 'completed' AND "tDrama-Genre"."Genre-ID" = 21 OR "tDrama-Genre"."Genre-ID" = 22)

Notice the last line where conditions are AND AND then OR. So it meet all the AND conditions or the OR condition and it is selected.

To fix is a matter of correctly grouping the conditions:

SELECT "tDramas"."TITLE",
       "tCountries"."COUNTRY",
       "tDramas"."SCORE",
       "tSTATUS"."Status",
       "tGenre"."GENRE",
       "tDrama-Genre"."Drama-ID"
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.75
AND   "tSTATUS"."Status" = 'completed'
AND   ("tDrama-Genre"."Genre-ID" = 21 OR "tDrama-Genre"."Genre-ID" = 22)

Now you must meet ALL the AND’s with the last being "Genre-ID" = 21 or "Genre-ID" = 22

UncleRobin · October 7, 2021, 12:09am

Thank you so much! I have a lot of queries in mind involving comparisons of percentages and the like, so I really want to understand how to write queries rather than build them in Design View, since I know it’s much less flexible. It’s going to take me a while to wrap my head around that sort of layered SQL syntax, so I’m starting with queries that I can build id Design View and trying to learn from the SQL generated.

Your reply has not only helped me understand this particular syntax problem, but also nicely illustrated why thefrugalcomputerguy tutorials make a point of reformatting the SQL in Base to make it easier to read and parse. VERY much appreciated.

Wanderer · October 7, 2021, 5:45am

If you write the query in SQL - not using design mode - it is possible to set direct-SQL-Mode, where Base is not altering your query. Then also the formatting is kept.

You loose Parameters :askMe and obviously everything, where Base has to understand the query. But to check out tutorials on SQL it is great.

As you can try the query even before modifications are saved it is a quite safe playground…

UncleRobin · October 7, 2021, 9:25am

A very handy tip, thanks!

flywire · October 7, 2021, 10:20am

As you say, some SQL isn’t valid in Design View but even then you can normally test parts of it in [QBE] Design View as you build up the SQL.

UncleRobin · October 7, 2021, 1:23pm

Thanks. As with learning any new language, mistakes are big part of the learning process. Figuring out what I did wrong and why it was wrong will hopefully help both my neurons grasp SQL a bit better.

Ratslinger · October 7, 2021, 3:43pm

Setting Direct is not necessary. You just need to keep a second copy of the SQL surrounded by comment indicators. See → Multi-line Comments. Single and multi-line comments may vary depending upon the DB used. Here is HSQLDB embedded sample:

Should not be used. It is hardly valid for anything more than HSQLDB embedded (and issues even there). Just learn and use SQL and quit using a crutch.