GROUP_CONCAT and DISTINCT are great, but how do I get rid of these duplicates I have?
I have a mysql table set up like this:
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 result to look like:
20 corporate, business, strategy, bowser, flowers, battleship, dungeon
but the closest I got 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
Anyone have a solution? thanks for a ton in advance!
source to share
What you are doing is not possible with pure SQL, how you structured your data.
No SQL implementation will treat "Enterprise" and "Enterprise, Business" and treat them as equal strings. Therefore, different ones will not work.
If you can manage the database,
The first thing I would like to do is change the data setting:
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
It will be even better
id uid keywordId
1 20 1
2 20 1
2 20 2
2 20 3
with a separate table for keywords
KeywordID KeywordText
1 Corporate
2 Business
Otherwise, you will need to bulk the data in your code.
source to share
You have two options as I see it.
Option 1:
Change the way your data is stored (keywords in their own table, join an existing table with keywords table using a many-to-many relationship). This will allow you to use DISTINCT. DISTINCT does not work at this time because the query sees "corporate" and "corporate, business, strategy" as two different meanings.
Option 2:
Write "interesting" sql to split keyword strings. I don't know what the limitations are in MySQL, but SQL is not meant to do this at all.
source to share