CODEWITHSUNDEEP
mysql
Ali Murtaza
Ali Murtaza

Reputation: 221

matching comma separated string to ids mysql

I have 2 tables

  1. Users
  2. Locations

now 90% of the time 1 user is connected to 1 location. But now a scenario has come up where 1 user has 2 locations

I have done this

Locations table
id     name
1      A
2      B
3      C

users table

id     location_id
1      1
2      1
3      1,2

now i want to do a query where i can match a comma separated string like 1,2 to the table locations an get 1 and 2.

What is the best way to do this?

Upvotes: 1

Views: 2238

Answers (3)

Khawer Zeshan
Khawer Zeshan

Reputation: 9646

You can use FIND_IN_SET

SELECT * FROM users u JOIN locations l ON FIND_IN_SET(l.id, u.location_id) > 0

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269633

Bad choice of data format. You are storing lists of things in a string, when SQL has a great structure for storing lists. It is called a table, and specifically a junction table in this case. Plus, you are storing (presumably) numeric ids as strings.

None the less, you can do what you want:

select u.*, l.name
from users u join
     locations l
     on find_in_set(l.id, u.location_id) > 0;

Performance will not be good, but that is part of the price of having a poor data layout.

Upvotes: 1

Frank Riccobono
Frank Riccobono

Reputation: 1063

If you have the power to change your schema slightly, the best thing to do would be to normalize your tables by creating a separate table mapping users to locations. That way it becomes:

Locations
ID     name
1       A
2       B
...

Users
ID      name (or whatever)
1       jsmith
2       jdoe

UsersLocations
ID     user_id        location_id
1       1                  1
1       2                  1
1       2                  2

Upvotes: 1

Related Questions