[MYSQL] Select users who own both a dog and a cat
- by matte
Hi,
I have this sample table:
CREATE TABLE `dummy` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`userId` int(11) NOT NULL,
`pet` varchar(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;
INSERT INTO `dummy` (`id`, `userId`, `pet`) VALUES(1, 1, 'dog');
INSERT INTO `dummy` (`id`, `userId`, `pet`) VALUES(2, 1, 'cat');
INSERT INTO `dummy` (`id`, `userId`, `pet`) VALUES(3, 2, 'dog');
INSERT INTO `dummy` (`id`, `userId`, `pet`) VALUES(4, 2, 'cat');
INSERT INTO `dummy` (`id`, `userId`, `pet`) VALUES(5, 3, 'cat');
INSERT INTO `dummy` (`id`, `userId`, `pet`) VALUES(6, 4, 'dog');
How can I write the statements below in mysql:
Retrieve all users who own both a dog and a cat
Retrieve all users who own a dog or a cat
Retrieve all users who own only a cat
Retrieve all users who doesn't own a cat
Thanks!
