如何从这种关系中选择条目? [英] How to select entries from this relationship?
本文介绍了如何从这种关系中选择条目?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有这四个表:提要,feed_entries,entries_categorias和categorias.这些结构:
I have these four tables: feeds, feed_entries, entries_categorias and categorias. Whith these structures:
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
但我不想指定仅一种关系,而是要指定两个或更多...
But instead of especify just one cateogory from the relationship, I want to specify two or more...
换句话说,我该如何选择恰好包括在这两个类别中的所有提要条目,例如:Google和Apple.这些条目可以具有其他类别,但需要具有这两个类别.
In other words, how can I select all feed entries that was included in exactly this two categories, for example: Google and Apple. These entries can have other categories, but need to have these two.
推荐答案
尝试如下操作:
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
)
这篇关于如何从这种关系中选择条目?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文