php中的内部连接

Question

SELECT thread.id AS p_id, post.title, post.content, author.username, author.id AS a_id
                         FROM thread INNER JOIN author
                         ON author_id = author.id
                         ORDER BY thread.created DESC
                         LIMIT $start, 30

我有一个名为posts，posts_id和tags的表。

我如何扩展上面的SQL语句，使用内部连接( ...or )获得与每个帖子相关的所有标记？

Answer 1

试试这个(假设包含标签信息的表是标签，并且有一个thread_id列)：

SELECT thread.id AS p_id, 
             post.title, 
             post.content, 
             author.username, 
             author.id AS a_id,
             GROUP_CONCAT(DISTINCT tag_name ORDER BY tag_name DESC SEPARATOR ',') AS tags
 FROM thread INNER JOIN author
 ON author_id = author.id INNER JOING TAGS 
 ON thread.id = tags.thread_id
 GROUP BY thread.id
 ORDER BY thread.created DESC

 LIMIT $start, 30

编辑:将分组上移一行。

Answer 2

SELECT thread.id AS p_id，post.title，post.content，author.username，author.id AS a_id FROM thread LEFT JOIN author ON author_id = author.id LEFT JOIN tags ON tags.thread_id = thread.id ORDER BY thread.created DESC LIMIT $start，30

Sanil