現在sqlでデータ集計しているのですが、データが抜け落ちる原因がわからないです。
どなたかご教授ください。
sql
1inventory | 2 3 CREATE TABLE `inventory` ( 4 `inventory_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT, 5 `film_id` smallint(5) unsigned NOT NULL, 6 `store_id` tinyint(3) unsigned NOT NULL, 7 `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 8 PRIMARY KEY (`inventory_id`), 9 KEY `idx_fk_film_id` (`film_id`), 10 KEY `idx_store_id_film_id` (`store_id`,`film_id`), 11 CONSTRAINT `fk_inventory_film` FOREIGN KEY (`film_id`) REFERENCES `film` (`film_id`) ON DELETE RESTRICT ON UPDATE CASCADE, 12 CONSTRAINT `fk_inventory_store` FOREIGN KEY (`store_id`) REFERENCES `store` (`store_id`) ON DELETE RESTRICT ON UPDATE CASCADE 13) ENGINE=InnoDB AUTO_INCREMENT=4582 DEFAULT CHARSET=utf8 14 15film | 16 17 CREATE TABLE `film` ( 18 `film_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, 19 `title` varchar(255) NOT NULL, 20 `description` text, 21 `release_year` year(4) DEFAULT NULL, 22 `language_id` tinyint(3) unsigned NOT NULL, 23 `original_language_id` tinyint(3) unsigned DEFAULT NULL, 24 `rental_duration` tinyint(3) unsigned NOT NULL DEFAULT '3', 25 `rental_rate` decimal(4,2) NOT NULL DEFAULT '4.99', 26 `length` smallint(5) unsigned DEFAULT NULL, 27 `replacement_cost` decimal(5,2) NOT NULL DEFAULT '19.99', 28 `rating` enum('G','PG','PG-13','R','NC-17') DEFAULT 'G', 29 `special_features` set('Trailers','Commentaries','Deleted Scenes','Behind the Scenes') DEFAULT NULL, 30 `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 31 PRIMARY KEY (`film_id`), 32 KEY `idx_title` (`title`), 33 KEY `idx_fk_language_id` (`language_id`), 34 KEY `idx_fk_original_language_id` (`original_language_id`), 35 CONSTRAINT `fk_film_language` FOREIGN KEY (`language_id`) REFERENCES `language` (`language_id`) ON DELETE RESTRICT ON UPDATE CASCADE, 36 CONSTRAINT `fk_film_language_original` FOREIGN KEY (`original_language_id`) REFERENCES `language` (`language_id`) ON DELETE RESTRICT ON UPDATE CASCADE 37) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8 38 39
sql
1SELECT 2i.inventory_id 3,i.store_id 4,i.film_id 5,f.title 6FROM 7inventory as i 8left outer JOIN 9film as f 10on 11i.film_id=f.film_id 12limit 10 13;
どちらもfilm_id=2があるのにも関わらず、出力結果では削られてしまっているのがなぜかわかりません。。。
inventory
1+--------------+---------+----------+---------------------+ 2| inventory_id | film_id | store_id | last_update | 3+--------------+---------+----------+---------------------+ 4| 1 | 1 | 1 | 2006-02-15 05:09:17 | 5| 2 | 1 | 1 | 2006-02-15 05:09:17 | 6| 3 | 1 | 1 | 2006-02-15 05:09:17 | 7| 4 | 1 | 1 | 2006-02-15 05:09:17 | 8| 5 | 1 | 2 | 2006-02-15 05:09:17 | 9| 6 | 1 | 2 | 2006-02-15 05:09:17 | 10| 7 | 1 | 2 | 2006-02-15 05:09:17 | 11| 8 | 1 | 2 | 2006-02-15 05:09:17 | 12| 9 | 2 | 2 | 2006-02-15 05:09:17 | 13| 10 | 2 | 2 | 2006-02-15 05:09:17 | 14+--------------+---------+----------+---------------------+ 1510 rows in set (0.01 sec)
film
1+---------+------------------+-----------------------------------------------------------------------------------------------------------------------+--------------+-------------+----------------------+-----------------+-------------+--------+------------------+--------+----------------------------------+---------------------+ 2| film_id | title | description | release_year | language_id | original_language_id | rental_duration | rental_rate | length | replacement_cost | rating | special_features | last_update | 3+---------+------------------+-----------------------------------------------------------------------------------------------------------------------+--------------+-------------+----------------------+-----------------+-------------+--------+------------------+--------+----------------------------------+---------------------+ 4| 1 | ACADEMY DINOSAUR | A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies | 2006 | 1 | NULL | 6 | 0.99 | 86 | 20.99 | PG | Deleted Scenes,Behind the Scenes | 2006-02-15 05:03:42 | 5| 2 | ACE GOLDFINGER | A Astounding Epistle of a Database Administrator And a Explorer who must Find a Car in Ancient China | 2006 | 1 | NULL | 3 | 4.99 | 48 | 12.99 | G | Trailers,Deleted Scenes | 2006-02-15 05:03:42 | 6| 3 | ADAPTATION HOLES | A Astounding Reflection of a Lumberjack And a Car who must Sink a Lumberjack in A Baloon Factory | 2006 | 1 | NULL | 7 | 2.99 | 50 | 18.99 | NC-17 | Trailers,Deleted Scenes | 2006-02-15 05:03:42 | 7| 4 | AFFAIR PREJUDICE | A Fanciful Documentary of a Frisbee And a Lumberjack who must Chase a Monkey in A Shark Tank | 2006 | 1 | NULL | 5 | 2.99 | 117 | 26.99 | G | Commentaries,Behind the Scenes | 2006-02-15 05:03:42 | 8| 5 | AFRICAN EGG | A Fast-Paced Documentary of a Pastry Chef And a Dentist who must Pursue a Forensic Psychologist in The Gulf of Mexico | 2006 | 1 | NULL | 6 | 2.99 | 130 | 22.99 | G | Deleted Scenes | 2006-02-15 05:03:42 | 9| 6 | AGENT TRUMAN | A Intrepid Panorama of a Robot And a Boy who must Escape a Sumo Wrestler in Ancient China | 2006 | 1 | NULL | 3 | 2.99 | 169 | 17.99 | PG | Deleted Scenes | 2006-02-15 05:03:42 | 10| 7 | AIRPLANE SIERRA | A Touching Saga of a Hunter And a Butler who must Discover a Butler in A Jet Boat | 2006 | 1 | NULL | 6 | 4.99 | 62 | 28.99 | PG-13 | Trailers,Deleted Scenes | 2006-02-15 05:03:42 | 11| 8 | AIRPORT POLLOCK | A Epic Tale of a Moose And a Girl who must Confront a Monkey in Ancient India | 2006 | 1 | NULL | 6 | 4.99 | 54 | 15.99 | R | Trailers | 2006-02-15 05:03:42 | 12| 9 | ALABAMA DEVIL | A Thoughtful Panorama of a Database Administrator And a Mad Scientist who must Outgun a Mad Scientist in A Jet Boat | 2006 | 1 | NULL | 3 | 2.99 | 114 | 21.99 | PG-13 | Trailers,Deleted Scenes | 2006-02-15 05:03:42 | 13| 10 | ALADDIN CALENDAR | A Action-Packed Tale of a Man And a Lumberjack who must Reach a Feminist in Ancient China | 2006 | 1 | NULL | 6 | 4.99 | 63 | 24.99 | NC-17 | Trailers,Deleted Scenes | 2006-02-15 05:03:42 | 14+---------+------------------+-----------------------------------------------------------------------------------------------------------------------+--------------+-------------+----------------------+-----------------+-------------+--------+------------------+--------+----------------------------------+---------------------+ 15
result
1+--------------+----------+---------+------------------+ 2| inventory_id | store_id | film_id | title | 3+--------------+----------+---------+------------------+ 4| 1 | 1 | 1 | ACADEMY DINOSAUR | 5| 2 | 1 | 1 | ACADEMY DINOSAUR | 6| 3 | 1 | 1 | ACADEMY DINOSAUR | 7| 4 | 1 | 1 | ACADEMY DINOSAUR | 8| 16 | 1 | 4 | AFFAIR PREJUDICE | 9| 17 | 1 | 4 | AFFAIR PREJUDICE | 10| 18 | 1 | 4 | AFFAIR PREJUDICE | 11| 19 | 1 | 4 | AFFAIR PREJUDICE | 12| 26 | 1 | 6 | AGENT TRUMAN | 13| 27 | 1 | 6 | AGENT TRUMAN | 14+--------------+----------+---------+------------------+ 1510 rows in set (0.01 sec)
回答3件
あなたの回答
tips
プレビュー