I have a table t1 that has an id and can be related to multiple categories with a table that has t1.id and categories.categoryid.In the categories table is a catname field.
What I would like is to do a query that returns a field in each row of t1 results that concatenates the categories to look like “cat1, cat2, cat3”.
My inclination is to handle it programatically to carry a field in t1 called categories and whenever I add or remove a category from that t1 record I would adjust the value of that t1.categories field. That way no query is necessary at all. And I will add that modifying categories after the initial setup is not a frequent occurence.
How bad is it in a case like this to create a situation of redundancy and manage it programatically?
SELECT t1.id
, t1.descr
, GROUP_CONCAT(cat.catname) AS categories
FROM t1
INNER
JOIN t1_cats
ON t1_cats.id = t1.id
INNER
JOIN categories AS cat
ON cat.id = t1_cats.cat_id
GROUP
BY t1.id
Not to belabor the point, but a similar situation. I have a primary table that has related records in a one to many relationship tied by a t1.id field carried in the sub table.
Whenever I retrieve a set from the primary table, I want to know how many records there are in the sub table. So I am assuming that the notion of keeping that count programatically in a t1 field is a very, very bad idea also.
Using your model, this is what I came up with for that
select t1.id, t1.name, count(p.t1id) as counter from t1 inner join t2 as p on p.t1id = t1.id group by p.t1id
And that seems to work fine. Is there a better or faster way to do that? Thanks again for the input.
it’s better to group on t1.id instead of p.t1id… in case you ever want to use a LEFT OUTER JOIN and return instances with 0 counts where there are no related child rows
Great, thanks for the input. Oh, and I have your book by the way. It was a great help when I started this a year ago and I think I will revisit it now that I have a little different perspective. cheers
Okay, it turns out to be more than that. I have a category/sub-category relationship. So what I need is to concatanate the category-subcat names as the returned value like “catname-subcatname”.
I tried this but get an ‘unknown column categories’ error
select t1.forumid,
GROUP_CONCAT(cat.catname) AS categories,
CONCAT(categories, '-', subcatname) AS fullcatname
from forums as t1
INNER JOIN forumsubcats as t2
ON t2.forumid = t1.forumid
INNER JOIN subcats as t3
ON t2.subcatid = t3.subcatid
INNER
JOIN categories AS cat
ON cat.categoryid = t3.categoryid
GROUP
BY t1.forumid
Then once I get that working, I need to combine it with the other query to get the record count of another sub table as count. When I tried to incorporate that, it breaks the category query that I had. I think it’s because of the group by, not quite sure how to handle multiple group by statements.
Any help would be appreciated. If that’s not clear, let me know. Basically the structure is
forums as t1 - forumid
forumsubcats as t2 - forumid, subcatid
subcats as t3 - subcatid, categoryid, subcatname
categories as cat - categoryid, catname
then the other table that I need to get the count from is called
posts as p - postid, forumid
Okay, I just stumbled on the first answer as such (got this from the book ). So now how do I incorporate the count query to find the count of rows in the posts table?
select t1.forumid,
GROUP_CONCAT(cat.catname, '-', subcatname) AS categories
from forums as t1
INNER JOIN forumsubcats as t2
ON t2.forumid = t1.forumid
INNER JOIN subcats as t3
ON t2.subcatid = t3.subcatid
INNER
JOIN categories AS cat
ON cat.categoryid = t3.categoryid
GROUP
BY t1.forumid
Okay, now that that one is fixed the other works fine as follows. Is this what it should look like?
select t1.forumid,
GROUP_CONCAT(cat.catname, '-', subcatname) AS categories,
count( p.forumid ) AS counter
from forums as t1
INNER JOIN forumsubcats as t2
ON t2.forumid = t1.forumid
INNER JOIN subcats as t3
ON t2.subcatid = t3.subcatid
INNER
JOIN categories AS cat
ON cat.categoryid = t3.categoryid
INNER JOIN posts as p on p.forumid = t1.forumid
GROUP
BY t1.forumid
Actually that one doesn’t work for the count. Or I should say I get the count fine but I get the group_concat for every record in the posts table. So if the count is 10, the categories field looks like “cat-sub, cat-sub, cat-sub, etc.”
select t1.forumid,
GROUP_CONCAT(distinct cat.catname, '-', subcatname) AS categories,
count( p.forumid ) AS counter
from forums as t1
INNER JOIN forumsubcats as t2
ON t2.forumid = t1.forumid
INNER JOIN subcats as t3
ON t2.subcatid = t3.subcatid
INNER
JOIN categories AS cat
ON cat.categoryid = t3.categoryid
INNER JOIN posts as p on p.forumid = t1.forumid
GROUP
BY t1.forumid
