Joining content items with varying lengths of comments and tags :D

I have a content table that can have varying result lengths of comments and tags associated with each row. I am unsure of how to get all of this information for a piece of content in one swoop.

I can easily make a query to get the content info (id) and query each table for the comments and tags but that doesn't seem like it makes much sense as if there is a better way.

Any tips or suggestions? Thanks in advance!

Thomas

My query SO far looks like:

SELECT c.*, GROUP_CONCAT(tagWords.tagWord SEPARATOR ', ') AS tags FROM platform.contents c
LEFT OUTER JOIN platform.contentTags ON contentTags.contentId = c.contentId
LEFT OUTER JOIN platform.tagWords ON contentTags.tagId = tagWords.tagId
WHERE  c.contentType = 'album' LIMIT 10

GROUP_CONCAT is great and all but I need the fields from each table and as the tables will grow. :/

My table layouts:

mysql> explain contentComments;
+--------------+---------------------+------+-----+-------------------+-------+
| Field        | Type                | Null | Key | Default           | Extra |
+--------------+---------------------+------+-----+-------------------+-------+
| contentId    | bigint(19) unsigned | NO   | MUL | NULL              |       |
| userId       | bigint(19) unsigned | NO   |     | NULL              |       |
| message      | varchar(255)        | YES  |     | NULL              |       |
| stampCreated | timestamp           | NO   |     | CURRENT_TIMESTAMP |       |
+--------------+---------------------+------+-----+-------------------+-------+
4 rows in set (0.00 sec)

mysql> explain contentTags;
+-----------+------------+------+-----+---------+-------+
| Field     | Type       | Null | Key | Default | Extra |
+-----------+------------+------+-----+---------+-------+
| contentId | bigint(20) | NO   | MUL | NULL    |       |
| tagId     | bigint(20) | YES  |     | NULL    |       |
+-----------+------------+------+-----+---------+-------+
   2 rows in set (0.00 sec)

mysql> explain contents;
+------------------+-----------------------------------------+------+-----+-------------------+----------------+
| Field            | Type                                    | Null | Key | Default                   | Extra          |
+------------------+-----------------------------------------+------+-----+-------------------+----------------+
| contentId        | bigint(20)                              | NO   | PRI | NULL              | auto_increment |
| contentType      | enum('video','album','blogpost','news') | NO   |     | NULL              |                |
| userId           | bigint(19) unsigned                     | NO   | MUL | NULL              |                |
| contentTitle     | varchar(45)                             | YES  |     | NULL              |                |
| contentDesc      | varchar(255)                            | YES  |     | NULL                  |                |
+------------------+-----------------------------------------+------+-----+-------------------+----------------+

mysql> explain tagWords;
+---------+---------------------+------+-----+---------+-------开发者_运维技巧---------+
| Field   | Type                | Null | Key | Default | Extra          |
+---------+---------------------+------+-----+---------+----------------+
| tagId   | bigint(19) unsigned | NO   | PRI | NULL    | auto_increment |
| tagWord | varchar(45)         | YES  |     | NULL    |                |
+---------+---------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

That looks right to me, though I think you also need a group_concat for comments. It could be that your brain isnt comfortable with the idea of N to N relationships in a database, which would be strange considering it looks to me like you did it right :p