我有三张桌子,下面有几列:
话题:
[主题名称]
信息:
[消息ID][消息文本]
消息主题关系
[entryid][messageid][topicid]
消息可以是关于多个主题的。问题是:给定两个主题,我需要得到关于所有这些主题的消息,而不是更少,但它们也可以是关于其他主题的。不会包含有关这些给定主题中某些主题的消息。我希望我能很好地解释我的要求。否则我可以提供样本数据。谢谢
最佳答案
下面使用x
、y
和z
代表主题id,因为没有提供任何示例。
使用联接:
SELECT m.*
FROM MESSAGES m
JOIN MESSAGETOPICRELATIONS mtr ON mtr.messageid = m.messageid
JOIN TOPICS tx ON tx.topicid = mtr.topicid
AND tx.topicid = x
JOIN TOPICS ty ON ty.topicid = mtr.topicid
AND ty.topicid = y
JOIN TOPICS tz ON tz.topicid = mtr.topicid
AND tz.topicid = z
使用分组方式/有计数(*):
SELECT m.*
FROM MESSAGES m
JOIN MESSAGETOPICRELATIONS mtr ON mtr.messageid = m.messageid
JOIN TOPICS t ON t.topicid = mtr.topicid
WHERE t.topicid IN (x, y, z)
GROUP BY m.messageid, m.messagetext
HAVING COUNT(*) = 3
在这两种方法中,连接方法更安全。
group by/having依赖于
MESSAGETOPICRELATIONS.TOPICID
作为主键的一部分,或者具有唯一的键约束以确保不存在重复项。否则,可以将同一主题的2+个实例关联到消息-这将是误报。使用HAVING COUNT(DISTINCT ...
可以清除任何误报,但支持取决于数据库-mysql支持5.1+,但不支持4.1+。Oracle可能要等到周一才能在SQL Server上进行测试…我查看了比尔关于不需要加入
TOPICS
表的评论:SELECT m.*
FROM MESSAGES m
JOIN MESSAGETOPICRELATIONS mtr ON mtr.messageid = m.messageid
AND mtr.topicid IN (x, y, z)
…将返回误报-至少与
IN
子句中定义的值之一匹配的行。以及:SELECT m.*
FROM MESSAGES m
JOIN MESSAGETOPICRELATIONS mtr ON mtr.messageid = m.messageid
AND mtr.topicid = x
AND mtr.topicid = y
AND mtr.topicid = z
…根本不会返回任何内容,因为
topicid
永远不能同时返回所有值。