'MySQLWorkbench returns 'OK' instead of returning rows

ok so, im trying to generate "TOP 5 name of childcare centres that is popular among the SC and SPR" and i got an "OK" instead of "xxx rows returned" in mySQLWorkbench output. Issit possible for anyone of you to check if my query is correct??

mySQLWorkbench version is 8.0.18

SELECT centre_service.centre_name
FROM centre_service
WHERE centre_service.centre_code = centre.centre_code  AND (type_of_citizenship = "SC" OR type_of_citizenship = "SPR") AND centre_name = (
SELECT centre.centre_name
FROM centre
GROUP BY centre_name LIMIT 5
);
mysqlsqlmysql-workbench


Solution 1:[1]

Try this query. You had 2 mistakes: using reference of a table from subquery in an outer scope (replaced to the inner scope) and using comparison operator to the multiple values (changed to IN).

SELECT centre_service.centre_name
FROM centre_service 
   LEFT JOIN (
      SELECT centre_name
      FROM centre
      GROUP BY centre_name LIMIT 5
   ) centre ON centre.centre_name = centre_service.centre_name
WHERE 
   (type_of_citizenship = "SC" OR type_of_citizenship = "SPR") AND
   centre_service.centre_code = centre.centre_code;

Solution 2:[2]

This answer does not tackle the errors in the query, but helps to answer the question about why neither an error is seen nor data rows, and what can be done about it.

MySQL Workbench does not handle all types of query errors nicely. Sometimes it just returns "OK" with no rows. I have seen this with, for example, illegal mixed collation errors.

When this happens, just issue this in the query tab:

show errors;

This will return one or more errors, each with a level, a code and a message. For example, my query (details not important) returned "OK" and nothing else. Then show errors gave me this:

Level Code Message
Error 1267 Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='

I cannot say for certain, but it seems like [some?] run-time errors are prone to this problem, rather than syntax errors that are caught immediately.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Jason

Related Questions