Unanswered: Help with 1 Post with multiple categories
I'm working on the development of a simple blog as part of a CMS and have stumbled upon an issue that I'm hoping someone here can help me with.
Here's what I'm trying to achieve and how the DB is currently setup.
I want to display records (blog posts) and also include the list of categories each post is in.
For example: New Web Site Released (post name) would display under the following categories: business information, web site releases, company news.
This is fairly easy to achieve for an individual blog post, as I have the post ID to filter things by. But, in the listing page I have multiple posts and as such am struggling on how to group the information for post categories below each blog post.
The database is setup with a one-to-many relationship as follows:
bl_id, bl_date, bl_synopsis, bl_post
tbl_BlogCategoryJoin (one-to-many table)
Is there a simple way of grouping this information via a MySQL statement?
FROM tbl_BlogPosts AS bp_table
JOIN ( SELECT bc_table.bl_id
, GROUP_CONCAT(bcj_table.bcName) AS categories
FROM tbl_BlogCategory AS bc_table
JOIN tbl_BlogCategoryJoin AS bcj_table
ON bcj_table.bc_id = bc_table.bc_id
BY bc_table.bl_id ) AS x_table
ON x_table.bl_id = bp_table.bl_id