SQL Syntax Query

Question

Can anyone advise where i'm going wrong with the syntax here please?

I have this query which returns 26 rows

SELECT firstname, lastname
  FROM author 
  WHERE authorid IN (
  SELECT authorid
  FROM written_by
  JOIN book ON written_by.bookdescid = book.bookdescid
  WHERE UPPER (book.title) LIKE UPPER ('%electric%')
  AND NOT UPPER (written_by.role) LIKE UPPER('%translator%'));

This appears to be correct and the reason I am using UPPER and ('%example%') like this is for variations in capitalization.

However, when I try and add into the query to also pick up where the book subtitle also includes 'electric' I am somehow getting the syntax wrong as it returns less rows? If anything it should be the same or more... I've tried a few variations but this one below is what i thought would work..

SELECT firstname, lastname
  FROM author 
  WHERE authorid IN (
  SELECT authorid
  FROM written_by
  JOIN book ON written_by.bookdescid = book.bookdescid
  WHERE UPPER (book.title) OR (book.subtitle) LIKE UPPER ('%electric%')
  AND NOT UPPER (written_by.role) LIKE UPPER('%translator%'));

What do you think that does & why? If you try to justify your expectations according to (authoritative) documentation (or, as a beginner, even an introduction) you would find you can't. One should always read the manual for functionality one is using. OR takes 2 conditions & has lower precedence than LIKE. PS For code questions a minimal reproducible example is expected. Which includes expectations with justification. Also minmal code with the problem like select * from t where x or y like 'A' would show the problem is OR vs LIKE. Or OR vs = even smaller. — philipxy
– philipxy, Commented Nov 7, 2020 at 11:20

forpas · Accepted Answer · 2020-11-07 11:27:07Z

1

You must use separate expressions with the operator LIKE:

  WHERE ((UPPER(book.title) LIKE '%ELECTRIC%') OR (UPPER(book.subtitle) LIKE '%ELECTRIC%'))
  AND (UPPER(written_by.role) NOT LIKE '%TRANSLATOR%');

Also use UPPER() only for the columns and provide the string literals in uppercase.

answered Nov 7, 2020 at 11:27

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Syntax Query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related