mysql - 在php中获取所有标签

Question

我的表结构与这个问题接受的答案相似:Recommended SQL database design for tags or tagging

除了我的是

thread, thread_tags and tags

使用下面的 SQL 命令我可以获得与每个线程关联的一个标签，如何获取与每个线程关联的所有标签？

SELECT 
  thread.id AS t_id, 
  thread.title, 
  thread.content, 
  author.username, 
  author.id AS a_id
FROM thread 
INNER JOIN author
  ON author_id = author.id
INNER JOIN thread_tags
  ON thread_id = thread.id
INNER JOIN tags
  ON tag_id = tags.id       
ORDER BY thread.created DESC

Answer 1

我认为您的联接看起来不错，但您不会从 tags 表中选择任何内容，除非您将它放在那里! T.* 下面

SELECT TH.id, TH.title, TH.content, A.username, A.id, T.*                  
FROM thread TH
INNER JOIN author A ON TH.author_id = A.id 
INNER JOIN thread_tags TT ON TT.thread_id = TH.id 
INNER JOIN tags T ON TT.tag_id = T.id 
ORDER BY TH.created DESC