GROUP_CONCAT and DISTINCT are great, but how do i get rid of these duplicates i still have?

Question

i have a mysql table set up like so:

id   uid   keywords
--   ---   ---
1    20    corporate
2    20    corporate,business,strategy
3    20    corporate,bowser
4    20    flowers
5    20    battleship,corporate,dungeon

what i WANT my output to look like is:

20    corporate,business,strategy,bowser,flowers,battleship,dungeon

but the closest i've gotten is:

SELECT DISTINCT uid, GROUP_CONCAT(DISTINCT keywords ORDER BY keywords DESC) AS keywords
 FROM mytable
 WHERE uid !=0
 GROUP BY uid

which outputs:

20    corporate,corporate,business,strategy,corporate,bowser,flowers,battleship,corporate,dungeon

does anyone have a solution? thanks a ton in advance!

Answer 1

What you're doing isn't possible with pure SQL the way you have your data structured.

No SQL implementation is going to look at "Corporate" and "Corporate, Business" and see them as equal strings. Therefore, distinct won't work.

If you can control the database,

The first thing I would do is change the data setup to be:

id   uid   keyword     <- note, not keyword**s** - **ONE** value in this column, not a comma delimited list
1    20    corporate
2    20    corporate
2    20    business
2    20    strategy

Better yet would be

id   uid   keywordId    
1    20    1
2    20    1
2    20    2
2    20    3

with a seperate table for keywords

KeywordID    KeywordText
1            Corporate
2            Business

Otherwise you'll need to massage the data in code.