SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Wizard gold trophysilver trophybronze trophy dc dalton's Avatar
    Join Date
    Nov 2004
    Location
    Right behind you, watching, always watching.
    Posts
    5,431
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    CONCAT within GROUP_CONCAT - AKA what's wrong with this picture

    I won't bore you with the details of why I'm trying to do this, let's just say I was brought in to a project to fix what is one MAJOR mess of an application and for reasons I won't go into right now the tables structure can't be changed at this time. (to be honest it should have never been done in MySQL and the person that created the tables should be 'serving fries' at McDonalds)

    Any way I am working on a view that pulls in data on a one to many relationship and have worked it so I only return one row for each entry in table one by using GROUP_CONCAT for the data in the second table. Works like a charm but then they added another 'feature' they want that makes it a touch tougher.

    Here's the view before (and working fine):

    Code MySQL:
    CREATE VIEW o_s_v AS
    	SELECT o.*,
    	GROUP_CONCAT(os.sc SEPARATOR ',') AS states,
    	GROUP_CONCAT(os.gmu SEPARATOR ',') AS gmus,
    	GROUP_CONCAT(os.county SEPARATOR ',') AS counties
    	FROM o, os WHERE o.oid = os.oid
    	GROUP BY oid

    So now they want another output that looks something like this:

    GMUS: {state}:{gmu}, {state}: {gmu} etc etc

    and

    Counties: {$state}:{county}, {$state}:{county} etc etc

    I THOUGHT I could do this:

    Code MySQL:
    CREATE VIEW o_s_v AS
    	SELECT o.*,
    	GROUP_CONCAT(os.sc SEPARATOR ',') AS states,
    	GROUP_CONCAT(CONCAT_WS(':', os.sc, os.gmu) SEPARATOR ',') AS gmus,
    	GROUP_CONCAT(CONCAT_WS(':', os.sc, os.county) SEPARATOR ',') AS counties
    	FROM o, os WHERE o.oid = os.oid
    	GROUP BY oid

    But alas MySQL is screaming bloody murder about that and now I'm trying to figure out how in the heck I am going to manage this.

    Am I correct in assuming that the group_concat can only take one field and not a combination as I have attempted?

    And if so anyone got a 'better idea' cause boy my heads starting to hurt!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by dc dalton View Post
    But alas MySQL is screaming bloody murder about that
    <!-- insert obligatory reference to Microsoft® CrystalBall© being down at the moment -->

    specific error message pls

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard gold trophysilver trophybronze trophy dc dalton's Avatar
    Join Date
    Nov 2004
    Location
    Right behind you, watching, always watching.
    Posts
    5,431
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    <!-- insert obligatory reference to Microsoft® CrystalBall© being down at the moment -->

    specific error message pls

    Sorry Rudy, been that kind of a day (ah heck who am I kidding, week, month) .. going 6 directions at once!

    I took a break and came back and hit the button again to submit the query .. before it was giving me a syntax error but I didn't change anything .. anyway here's the finished view in case anyone cares:

    Code MySQL:
    CREATE VIEW o_s_view AS
    	SELECT o.*,
    	GROUP_CONCAT(os.sc SEPARATOR ',') AS states,
    	GROUP_CONCAT(CONCAT_WS(':', os.sc, os.gmu) SEPARATOR ',') AS gmus,
    	GROUP_CONCAT(CONCAT_WS(':', os.sc, os.county) SEPARATOR ',') AS counties
    	FROM o, os WHERE o.oid = os.oid
    	GROUP BY oid

    I was fairly sure the syntax was right and have no idea why it didn't work the first 20 or 30 times I tried ... oh well, been the story of my life this month

  4. #4
    SitePoint Wizard gold trophysilver trophybronze trophy dc dalton's Avatar
    Join Date
    Nov 2004
    Location
    Right behind you, watching, always watching.
    Posts
    5,431
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    But you know what? Since I have your attention Rudy.

    The MySQL manual for concat_ws says:

    CONCAT_WS() does not skip empty strings. However, it does skip any NULL values after the separator argument.
    But I am getting returns for fields that are in fact NULL

    Basically what is being 'fed into' concat_ws is (':', 'OH', NULL)

    Yet I am getting back:

    OH

    Shouldn't that not be returning anything or am I misreading the manual


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
  •