CODEWITHSUNDEEP
mysqlsqldatabaseinner-join
Nathan Wiles
Nathan Wiles

Reputation: 926

INNER JOIN without restricting results

I'm slowly figuring out MySQL code, but I'm still iffy on a lot of things. What I want to do is select the most recent n entries into threads, and include a list of tag_ids any reference that particular entry.

I've frankenstein'd together a query which works as expected to some extent, but is restricting my list of threads returned to only entries which have tags associated with them.

SELECT th.thread_id,owner_id,message,time,tag_name
    FROM threads th
        INNER JOIN tag_thread_xref ttx
            ON th.thread_id = ttx.thread_id
        INNER JOIN tags t
            ON ttx.tag_id = t.tag_id
    ORDER BY time DESC
    LIMIT ?

I could obviousy just query for my list of threads, then make a second simpler query to find which ones have tags associated with them, but I'd like to achieve this with one query if possible. Is there a way to reword my SQL to make this work?

Upvotes: 0

Views: 139

Answers (1)

Michael Dunlap
Michael Dunlap

Reputation: 4310

Use LEFT JOIN to not exclude threads without tags. You will have NULL for the tag_name for any threads without a tag of course.

SELECT th.thread_id,owner_id,message,time,tag_name
FROM threads th
    LEFT JOIN tag_thread_xref ttx
        ON th.thread_id = ttx.thread_id
    LEFT JOIN tags t
        ON ttx.tag_id = t.tag_id
ORDER BY time DESC
LIMIT ?

If you want a "list" of tags for each thread, then you can use GROUP_CONCAT like this:

SELECT th.thread_id,owner_id,message,time, 
  GROUP_CONCAT(DISTINCT tag_name ORDER BY tag_name) as tags
FROM threads th
    LEFT JOIN tag_thread_xref ttx
        ON th.thread_id = ttx.thread_id
    LEFT JOIN tags t
        ON ttx.tag_id = t.tag_id
GROUP BY th.thread_id,owner_id,message,time
ORDER BY time DESC
LIMIT ?

Upvotes: 1

Related Questions