Link to home
Start Free TrialLog in
Avatar of skij
skijFlag for Canada

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');

Open in new window

I this example, how can I get a result list containing all birds (Aves)?
ASKER CERTIFIED SOLUTION
Avatar of Gauthier
Gauthier

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial