CODEWITHSUNDEEP
phpsqltags
user565031
user565031

Reputation: 11

Inner joins in php

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

I have a table called posts, posts_id and tags.

How can I extend the above SQL statement to get all the tags related to each post using INNER JOINS,...or something else?

Upvotes: 0

Views: 203

Answers (2)

Chandu
Chandu

Reputation: 82933

Try this(assumed the table with tag info to be tags and having a thread_id column):

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

EDIT: Moved GROUP By up one line.

Upvotes: 2

sshet
sshet

Reputation: 1160

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

Upvotes: 0

Related Questions