Mysql error 1111 in one version of query and error 1054 in another

Question

I have two tables:

• books: [isbn, book_title, publisher, ...]
• inventory: [isbn, date, num_changed]

I want to return book titles for those which are on stock. I tried a join (query 1) and got 1054 error, then I substituted the reference with the literal value and now I get 1111 error.

query 1:

SELECT `books`.`isbn`, `books`.`book_title`, SUM( `inventory`.`numbers_changed` ) AS `num`
FROM `books`
INNER JOIN `inventory` ON `books`.`isbn` = `inventory`.`isbn`
WHERE `books`.`publisher` LIKE '%pint%'
AND `num` > '0'

query 2:

SELECT `books`.`isbn`, `books`.`book_title`, SUM( `inventory`.`numbers_changed` )
FROM `books`
INNER JOIN `inventory` ON `books`.`isbn` = `inventory`.`isbn`
WHERE `books`.`publisher` LIKE '%print%'
AND SUM( `inventory`.`numbers_changed` ) > '0'

What's the correct query to use?

Edit
Here are the create table queries:

CREATE TABLE IF NOT EXISTS `books` (
  `isbn` varchar(30) CHARACTER SET ascii NOT NULL,
  `book_title` varchar(255) CHARACTER SET utf8 COLLATE utf8_persian_ci NOT NULL,
  `date_published` varchar(10) CHARACTER SET ascii NOT NULL,
  `author` varchar(40) CHARACTER SET utf8 COLLATE utf8_persian_ci NOT NULL,
  `translator` varchar(40) CHARACTER SET utf8 COLLATE utf8_persian_ci DEFAULT NULL,
  `publisher` varchar(50) CHARACTER SET utf8 COLLATE utf8_persian_ci NOT NULL,
  `ganre` varchar(50) CHARACTER SET utf8 COLLATE utf8_persian_ci NOT NULL,
  `price` int(7) unsigned NOT NULL,
  `cover_pic` int(1) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`isbn`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `inventory` (
  `isbn` varchar(30) CHARACTER SET ascii NOT NULL,
  `date` varchar(10) CHARACTER SET ascii NOT NULL,
  `numbers_changed` int(5) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Answer 1

The 1054 error is about referencing a column that doesn't exist. The actual error message would help to know what is causing the issue.

The 1111 error is because you're trying to use aggregate function (in this case, SUM) in the WHERE clause:

WHERE ...
  AND SUM( `inventory`.`numbers_changed` ) > '0'  
      ^
      |__ see this?

...outside of a subquery. SQL statements are checked from bottom to top, so I expect that removing the SUM in the WHERE clause will show that the 1054 error is still unaddressed.

Answer 2

use having for second where argument

WHERE `books`.`publisher` LIKE '%print%' 
HAVING ( COUNT(`inventory`.`numbers_changed`)  > '0')

instead of

WHERE `books`.`publisher` LIKE '%print%'
AND SUM( `inventory`.`numbers_changed` ) > '0'