0

I have a table with that looks like this:

|id | name | sport            |
|---|------|------------------|
|1  | john | kitesurf         |
|2  | mike | kitesurf         |
|3  | tobi | tennis, kitesurf |
|4  | mat  | surf, skate      |
|5  | bob  | tennis           |

How can I get a list with groups by sports that would look like this:

| sport     | name
|-----------|-------
| kitesurf  | john
| kitesurf  | mike
| kitesurf  | tobi
| tennis    | tobi
| tennis    | bob
| skate     | mat
| surf      | mat

Is it anyhow doable using just MySQL? Thanks for any tip :)

M_D1245
  • 1
  • 1
  • MySQL doesn't have any built-in function to split a string. You shouldn't use comma-separated lists, use a many-to-many relation table. – Barmar Dec 08 '16 at 23:30

1 Answers1

2

You'll need another table that lists each distinct sport.

CREATE TABLE Sports ( sport VARCHAR(10) PRIMARY KEY );
INSERT INTO Sports (sport) VALUES ('kitesurf'), ('tennis'), ('skate'), ('surf');

Then you can join this way:

SELECT s.sport, n.name
FROM NoOneNamesTheirTableInSqlQuestions AS n
INNER JOIN Sports AS s ON FIND_IN_SET(s.sport, n.sport);

But this is an inefficient query, because the FIND_IN_SET() cannot use an index.

See also my answer to Is storing a delimited list in a database column really that bad?

Community
  • 1
  • 1
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Many thanks to you @Bill. I understood my mistake and will avoid comma separated values in the future. But one subquestion then: why is there a "set" type for MySQL columns for if CSV are so bad? – M_D1245 Dec 09 '16 at 10:27
  • I'd guess that some customer of MySQL paid them to implement it. It may have been the least bad solution for their specific project. Note that you cannot use an index to search for a specific value in a `SET` datatype. If you have no need to search for a value in the set, it can be a compact way of storing a collection of options. – Bill Karwin Dec 09 '16 at 16:14