join with mulitple results concatenated into a string

[bostboy]

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?

[r937]

How bad is it in a case like this to create a situation of redundancy and manage it programatically?

very, very bad

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

[bostboy]

Somehow I knew you'd say that.

Thanks for the input.

[r937]

so did you understand my query? have you tried it?

[bostboy]

absolutely, works great. thanks so much for the help

[bostboy]

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.

[r937]

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

[bostboy]

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

[bostboy]

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

[bostboy]

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

[bostboy]

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

[bostboy]

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."

[bostboy]

Okay, I think I have it now. How does this look?

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

[bostboy]

That doesn't really work either. I am going to start a new thread on this because this is now a different problem than the original thread.