3

I have the below SQL query and it will return a group_name along with a list of departments for that group. I was able to lowercase the departments returned, but I can't figure out how to lowercase the group name as well.

Also, instead of lowercasing each returned column is there perhaps a way to lowercase the entire result in one swoop?

SELECT sg.group_name,A.dept_name
FROM   `sys_groups` `sg`
       INNER JOIN (SELECT gda.group_id,
       GROUP_CONCAT(LOWER(sd.dept_name) ORDER BY `dept_name`
                  SEPARATOR '|'
                  ) `dept_name`
       FROM   `group_dept_access` `gda`
       INNER JOIN `sys_department` `sd`
       ON gda.dept_id = sd.dept_id
       GROUP  BY gda.group_id) AS `A`
       ON sg.group_id = A.group_id

Thank you in advance!

Saharsh Shah
  • 28,687
  • 8
  • 48
  • 83
kittycat
  • 14,983
  • 9
  • 55
  • 80
  • This is totally unrelated to the question, but the creepy stuff is an unfortunate consequence of showing you're a girl. Like in gaming, it's bound to happen, which is why some girls prefer to just use nicknames and avatars that don't reveal their gender. If people are really bothering you, that could be an option. Also, on behalf of all men, I apologize for the creepy stuff. – NullUserException Jan 03 '13 at 17:45
  • @NullUserException I usually ignore creeps, but he was just really bothering me with the invites to chat while I was trying to chat with others. I'll leave the chat system next time as my option. However, I personally don't believe any girl should have to resort to hiding behind a faux identity because of people like that. Oh, and apology accepted =oP – kittycat Jan 03 '13 at 18:00

1 Answers1

1

Try this: 

SELECT LOWER(sg.group_name) group_name, LOWER(A.dept_name) dept_name
FROM sys_groups sg
INNER JOIN (SELECT gda.group_id,
                   GROUP_CONCAT(sd.dept_name ORDER BY dept_name SEPARATOR '|') dept_name
            FROM group_dept_access gda
            INNER JOIN sys_department sd ON gda.dept_id = sd.dept_id
            GROUP BY gda.group_id
          ) AS A ON sg.group_id = A.group_id
Saharsh Shah
  • 28,687
  • 8
  • 48
  • 83