CODEWITHSUNDEEP
mysqlsortinggroup-by
M_D1245
M_D1245

Reputation: 1

MySQL GROUP BY FIND_IN_SET

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 :)

Upvotes: 0

Views: 1696

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562388

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?

Upvotes: 2

Related Questions