2

I have tables called person and book and image and bookhit.

Person has id, name and Book has id, owner_id, info and Image has a column for id, owner_id, url and thumbnail which is a TINYINT (In the database half the rows are 0s and 1s.) By the way, the image column stores images of the cover of the book in two version: big-one and thumbnail. The table bookhit stores the times the book has been retrieved from the database and has a column hits.

So I tried multiple INNER JOIN to retrieve all the thumbnails for the most popular books. The SQL Query is the following:

SELECT `imagehit`.`hits`, `person`.`name`, `book`.`info`, `image`.`url`, `image`.`thumbnail` FROM `imagehit`
INNER JOIN `person` ON `person`.`id`=`book`.`owner_id`
INNER JOIN `image` ON `image`.`owner_id`=`book`.`id`
ORDER BY `imagehit`.`hits` DESC
WHERE `image`.`thumbnail`=1
LIMIT 10;

And that doesn't work, even though half rows has 1s in image.thumbnail . If I change the following line:

WHERE `image`.`thumbnail`=1

To 

WHERE `image`.`thumbnail`=0

It does work. Well, I went to the image table and did a simple query like the following:

SELECT * FROM `image` WHERE `image`.`thumbnail`=0;

And gave me total rows stored in the table. But when I browse image table in phpMyAdmin I see there are 1s stored in the table. :(

Any ideas why this happens? thank you in advance.

Table definitions:

CREATE TABLE `image` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `owner_id` int(11) NOT NULL,
 `thumbnail` tinyint(1) NOT NULL,
 `url` varchar(255) NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `image_url` (`url`),
 KEY `image_owner_id` (`owner_id`),
 CONSTRAINT `image_ibfk_1` FOREIGN KEY (`owner_id`) REFERENCES `book` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1450 DEFAULT CHARSET=utf8

CREATE TABLE `person` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(255) NOT NULL,
 `url` varchar(60) NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `person_url` (`url`),
) ENGINE=InnoDB AUTO_INCREMENT=6287 DEFAULT CHARSET=utf8

CREATE TABLE `book` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `owner_id` int(11) NOT NULL,
 `book` varchar(3000) NOT NULL,
 `info` varchar(3000) NOT NULL,
 `url` varchar(60) NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `book_url` (`url`),
 KEY `book_owner_id` (`owner_id`),
 CONSTRAINT `book_ibfk_1` FOREIGN KEY (`owner_id`) REFERENCES `person` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=725 DEFAULT CHARSET=utf8

CREATE TABLE `imagehit` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `owner_id` int(11) NOT NULL,
 `person_id` int(11) NOT NULL,
 `hits` int(11) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `imagehit_person_id` (`person_id`),
 KEY `imagehit_owner_id` (`owner_id`),
 KEY `hits` (`hits`),
 CONSTRAINT `imagehit_ibfk_1` FOREIGN KEY (`owner_id`) REFERENCES `image` (`id`),
 CONSTRAINT `imagehit_ibfk_2` FOREIGN KEY (`person_id`) REFERENCES `person` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=725 DEFAULT CHARSET=utf8

Proof I'm not crazy: enter image description here

I inserted the data using Peewee, when I created the row I set thumbnail=True if the image was a thumbnail and as thumbnail=False if it wasn't. The column thumbnail is the field BooleanField in Peewee.

gglasses
  • 826
  • 11
  • 30
  • 1
    Show us your table definition. – Tim Biegeleisen Feb 11 '16 at 06:44
  • @TimBiegeleisen thank you for your interest. I edited my question and the definitions are there. :-) – gglasses Feb 11 '16 at 06:56
  • Are you _certain_ that you ever had `1`s in the table? How did you insert the data in the first place? – Tim Biegeleisen Feb 11 '16 at 06:59
  • @TimBiegeleisen I edited the question. I'm positive there are `1`s in the table. I added a picture of it and I added the rows using peewee. `thumbnail=True` or `thumbnail=False`. – gglasses Feb 11 '16 at 07:08
  • Your query is inconsistent with the screen capture, and I would be inclined to believe the query. – Tim Biegeleisen Feb 11 '16 at 07:13
  • What does `SELECT \`image\`.\`thumbnail\` FROM \`image\`;` show you? – Tim Biegeleisen Feb 11 '16 at 07:21
  • It shows all the records with the proper values. I first add the normal image so the first row shows `0` and the second row (the thumbnail for the first image show `1` and so on... – gglasses Feb 11 '16 at 07:27
  • OK my last attempt: Try using `WHERE \`image\`.\`thumbnail\`= TRUE` ... what do you get now? – Tim Biegeleisen Feb 11 '16 at 07:33
  • I'll check my Python code because when I do `WHERE image.thumbnail=TRUE` returns all the rows (the total in the table as 1. Then I do `WHERE image.thumbnail=FALSE` returns all the rows as 0. Sigh, something went very wrong. – gglasses Feb 11 '16 at 07:37
  • 1
    Your observations are not consistent with what you think the state of your data is. Yes, please check the Python code, and you might want to open a new question with this information. – Tim Biegeleisen Feb 11 '16 at 07:38
  • There are typos in the `SELECT` -- you don't have an `ON` for `imagehit`, nor a `JOIN book`. Please fix. – Rick James Feb 14 '16 at 05:06

0 Answers0