我有这四个表:提要,feed_entries,entries_categorias和categorias。这些结构:
CREATE TABLE `categorias` ( `id` int(11) NOT NULL auto_increment, `nome` varchar(100) collate utf8_unicode_ci NOT NULL, `slug` varchar(100) collate utf8_unicode_ci NOT NULL, `principal` int(1) NOT NULL default '0', `ordem` int(11) NOT NULL default '0', PRIMARY KEY (`id`), UNIQUE KEY `nome` (`nome`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; CREATE TABLE `entries_categorias` ( `id` int(11) NOT NULL auto_increment, `entry_id` int(11) NOT NULL, `categoria_id` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `entry_id` (`entry_id`), KEY `categoria_id` (`categoria_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE `feeds` ( `id` int(11) NOT NULL auto_increment, `categoria_id` int(11) NOT NULL, `titulo` varchar(255) collate utf8_unicode_ci NOT NULL, `descricao` text collate utf8_unicode_ci NOT NULL, `link` varchar(255) collate utf8_unicode_ci NOT NULL, `link_comentarios` varchar(255) collate utf8_unicode_ci NOT NULL, `url` varchar(255) collate utf8_unicode_ci NOT NULL, PRIMARY KEY (`id`), KEY `categoria_id` (`categoria_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; CREATE TABLE `feed_entries` ( `id` int(11) NOT NULL auto_increment, `feed_id` int(11) NOT NULL, `colunista_id` int(11) NOT NULL, `titulo` varchar(255) collate utf8_unicode_ci NOT NULL, `descricao` text collate utf8_unicode_ci NOT NULL, `slug` varchar(255) collate utf8_unicode_ci NOT NULL, `link` varchar(255) collate utf8_unicode_ci NOT NULL, `permaLink` varchar(255) collate utf8_unicode_ci NOT NULL, `html` text collate utf8_unicode_ci NOT NULL, `tags` varchar(255) collate utf8_unicode_ci NOT NULL, `imagemChamada` int(1) NOT NULL, `imagem332x332` int(1) NOT NULL, `imagem201x144` int(1) NOT NULL, `imagem145x145` int(1) NOT NULL, `imagem101x76` int(1) NOT NULL, `date` datetime NOT NULL, `created_at` datetime NOT NULL, `comments` int(11) NOT NULL, `comments_date` datetime NOT NULL, `views` int(11) NOT NULL, `deleted` int(1) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `permaLink` (`permaLink`), KEY `feed_id` (`feed_id`), KEY `colunista_id` (`colunista_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; ALTER TABLE `entries_categorias` ADD CONSTRAINT `entries_categorias_ibfk_5` FOREIGN KEY (`entry_id`) REFERENCES `feed_entries` (`id`) ON DELETE CASCADE, ADD CONSTRAINT `entries_categorias_ibfk_6` FOREIGN KEY (`categoria_id`) REFERENCES `categorias` (`id`) ON DELETE CASCADE; ALTER TABLE `feeds` ADD CONSTRAINT `feeds_ibfk_2` FOREIGN KEY (`categoria_id`) REFERENCES `categorias` (`id`) ON DELETE CASCADE; ALTER TABLE `feed_entries` ADD CONSTRAINT `feed_entries_ibfk_1` FOREIGN KEY (`feed_id`) REFERENCES `feeds` (`id`) ON DELETE CASCADE, ADD CONSTRAINT `feed_entries_ibfk_2` FOREIGN KEY (`colunista_id`) REFERENCES `colunistas` (`id`) ON DELETE CASCADE;
我需要做这样的选择:
SELECT e.* FROM feed_entries AS e INNER JOIN feeds AS f ON e.feed_id =f.id INNER JOIN entries_categorias AS ec ON ec.entry_id =e.id INNER JOIN categorias AS c ON ec.categoria_id =c.id WHERE (c.nome ='Manchete') AND (e.deleted =0) ORDER BY e.date DESC
但是,我不想指定仅一种关系类别,而是要指定两个或多个类别…
换句话说,我该如何选择恰好包括在这两个类别中的所有提要条目,例如:Google和Apple。这些条目可以具有其他类别,但是需要具有这两个类别。
尝试这样的事情:
SELECT * FROM `feed_entries` WHERE id IN ( SELECT e.id FROM `feed_entries` AS `e` INNER JOIN `feeds` AS `f` ON e.feed_id =f.id INNER JOIN `entries_categorias` AS `ec` ON ec.entry_id =e.id INNER JOIN `categorias` AS `c` ON ec.categoria_id =c.id WHERE c.nome IN ('Google','Apple') AND (e.deleted =0) GROUP BY e.id HAVING COUNT(DISTINCT ec.id) = 2 )