如何选择我的内容以及随附的所有标签？

Question

I've tried very hard to have a normalized database and currently have three tables like so -

1. content (c)
2. content_tags (ct)
3. tags (t)

What I'm trying to do here is select some rows and return them with all the tags that are associated with it. At the moment, it only returns one tag for some reason.

Here is the code I'm using:

   SELECT *, 
          GROUP_CONCAT(t.tag) AS tags
     FROM content AS c 
LEFT JOIN category AS ca ON c.cid = ca.cid 
LEFT JOIN contenttags AS ct ON c.smid = ct.smid 
LEFT JOIN tags AS t ON ct.tid = t.tid 
LEFT JOIN users AS u ON c.uid = u.uid
    WHERE (t.tag IN ('tag1', 'tag2', 'tag3'))
 GROUP BY c.smid

It returns something like this:

• cid
• content
• url
• tag (but only one instead of multiple)

Answer 1

Do you mean to find all contents and all their tags, where at least one of their tag is in $tagArray ?

   SELECT c.*, ca.*, u.*, 
          GROUP_CONCAT(t.tag) AS tags
     FROM content AS c 
LEFT JOIN category AS ca ON c.cid = ca.cid 
     JOIN contenttags AS ct ON c.smid = ct.smid 
     JOIN tags AS t ON ct.tid = t.tid 
LEFT JOIN users AS u ON c.uid = u.uid
    WHERE EXISTS ( SELECT *
                   FROM contenttags ct2
                     JOIN tag t2
                       ON t2.tid = ct2.tid
                   WHERE ct2.smid = c.smid
                     AND ct2.tag IN ('$tagArray')
                 )
 GROUP BY c.smid

And those LEFT JOIN can be probably turned into inner JOINs, too, without losing any results. Try it. Unless category.cid or category.uid fields can have NULL values. In that case, keep the related LEFT JOIN.