skij
asked on
MySQL: Select all birds
Using MySQL, in the example below, how can I select all items with the `Class` of "Aves"? The tricky part is that some items do not have their `class` specified however all members of the same `genus`are always members of the same `class`.
CREATE TABLE `creatures` (
`id` bigint(19) NOT NULL AUTO_INCREMENT,
`class` varchar(255) COLLATE utf8_unicode_ci NULL,
`genus` varchar(255) COLLATE utf8_unicode_ci NULL,
`species` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
INSERT INTO `creatures` VALUES
('1','','Pristis','P. pectinata', 'Smalltooth Sawfish'),
('2','','Chloris','C. chloris','European Greenfinch'),
('3','Chondrichthyes','Pristis','P. pristis', 'Common Sawfish'),
('4','Aves','Corvus','C. albus','Pied Crow'),
('5','Mammalia','Sylvilagus','S. nuttallii','Mountain Cottontail'),
('6','Chondrichthyes','Carcharodon','C. carcharias','Great White Shark'),
('7','','Pristis','P. clavata','Dwarf Sawfish'),
('8','Aves','Bubo','B. virginianus','Great Horned Owl'),
('9','','Chloris','C. ambigua','Black-headed Greenfinch'),
('10','Aves','Chloris','C. sinica','Grey-capped Greenfinch'),
('12','','Sylvilagus','S. brasiliensis','Tapeti'),
('13','','Pristis','P. zijsron', 'Longcomb sawfish'),
('14','Aves','Corvus','C. caurinus','Northwestern Crow'),
('15','','Sylvilagus','S. bachmani','Brush Rabbit'),
('16','','Corvus','C. woodfordi','White-billed Crow'),
('17','','Bubo','B. scandiacus','Snowy Owl'),
('18','','Bubo','B. capensis','Cape Eagle-owl');
I this example, how can I get a result list containing all birds (Aves)?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.