SitePoint Sponsor

User Tag List

Results 1 to 14 of 14
  1. #1
    SitePoint Evangelist
    Join Date
    Nov 2009
    Posts
    471
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    join with mulitple results concatenated into a string

    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?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by bostboy View Post
    How bad is it in a case like this to create a situation of redundancy and manage it programatically?
    very, very bad



    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist
    Join Date
    Nov 2009
    Posts
    471
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Somehow I knew you'd say that.

    Thanks for the input.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    so did you understand my query? have you tried it?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Evangelist
    Join Date
    Nov 2009
    Posts
    471
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    absolutely, works great. thanks so much for the help

  6. #6
    SitePoint Evangelist
    Join Date
    Nov 2009
    Posts
    471
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    Code:
    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.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Evangelist
    Join Date
    Nov 2009
    Posts
    471
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  9. #9
    SitePoint Evangelist
    Join Date
    Nov 2009
    Posts
    471
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

    Code:
    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

    Code:
    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

  10. #10
    SitePoint Evangelist
    Join Date
    Nov 2009
    Posts
    471
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

    Code:
    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

  11. #11
    SitePoint Evangelist
    Join Date
    Nov 2009
    Posts
    471
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay, now that that one is fixed the other works fine as follows. Is this what it should look like?

    Code:
    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

  12. #12
    SitePoint Evangelist
    Join Date
    Nov 2009
    Posts
    471
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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."

  13. #13
    SitePoint Evangelist
    Join Date
    Nov 2009
    Posts
    471
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay, I think I have it now. How does this look?

    Code:
    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

  14. #14
    SitePoint Evangelist
    Join Date
    Nov 2009
    Posts
    471
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •