CODEWITHSUNDEEP
mysqljoin
john mossel
john mossel

Reputation: 2156

Easier way of using Joins

I have the following tables:

Author:

id username email password salt email_salt email_verified ip_address

Author_threads:

thread_id, author_id

Thread:

id, title, content, created

Tag:

id, name

Thread_tags:

tad_id, thread_id

I want to select the latest 30 threads, their author and all their tags. This is the SQL statement I use:

       SELECT thread.title, thread.id as thread_id,
       thread.content, author.username, author.id as author_id,
       GROUP_CONCAT(DISTINCT tag.name ORDER BY tag.name DESC SEPARATOR ',') AS tags
       FROM thread 
       JOIN thread_tags ON thread.id = thread_tags.thread_id
       JOIN tag ON thread_tags.tag_id = tag.id

       JOIN author_threads ON thread.id = author_threads.thread_id
       JOIN author ON author_threads.author_id = author.id

       GROUP BY thread.id DESC
       LIMIT 0, 30

Is there an easier way of doing this?

Upvotes: 0

Views: 58

Answers (2)

Gnat
Gnat

Reputation: 2921

The only other option you have is to use a multi-table select, e.g.

   SELECT thread.title, thread.id as thread_id,
   thread.content, author.username, author.id as author_id,
   GROUP_CONCAT(DISTINCT tag.name ORDER BY tag.name DESC SEPARATOR ',') AS tags
   FROM thread, thread_tags, tag, author_threads, author
   WHERE thread.id = thread_tags.thread_id
       AND thread_tags.tag_id = tag.id
       AND thread.id = author_threads.thread_id
       AND author_threads.author_id = author.id
   GROUP BY thread.id DESC
   LIMIT 0, 30

Support for this may be database-dependent though.

Upvotes: 0

MatBailie
MatBailie

Reputation: 86765

I assume the GROUP BY should be ORDER BY?

Other than that, I do believe you're doing it the right why.

As you need values from all of the tables, you need to join them all, which is what you've done.

Upvotes: 0

Related Questions