mysql join get column value from comma separated value

Question

I have two tables as below :

vehicles :

vehicle_id   number_plate
1            B 101 RA
2            B 501 JU
3            B 401 JA
4            B 201 RU

team :

team_id      team_name    available_vehicles
1            A-001        1,2
2            A-002        NULL
3            A-003        4

I want to get value where the position of vehicle in the team table, my desired output would look like this:

vehicle_id ║ number_plate ║ team_name
1          ║ B 101 RA     ║ A-001
2          ║ B 501 JU     ║ A-001
3          ║ B 401 JA     ║ NULL
4          ║ B 201 RU     ║ A-003

you should normalize your available_vehicles column

ScaisEdge
– ScaisEdge

2018-02-17 10:26:48 +00:00
Commented Feb 17, 2018 at 10:26 — ScaisEdge
– ScaisEdge, Commented Feb 17, 2018 at 10:26
And think about this: Can teams share a vehicle?

KIKO Software
– KIKO Software

2018-02-17 10:27:20 +00:00
Commented Feb 17, 2018 at 10:27 — KIKO Software
– KIKO Software, Commented Feb 17, 2018 at 10:27

slaakso · Accepted Answer · 2018-02-17 10:36:31Z

2

You can use find_in_set-function, but that would be quite inefficient for larger datasets.

select v.vehicle_id, v.number_plate, t.team_name
from vehicles v
  join team t on find_in_set(v.vehicle_id, t.available_vehicles);

You should consider creating proper database structure with a separate table for

create table team_vehicles (
team_id int, 
vehicle_id int,
primary key(team_id, vehicle_id)
)

answered Feb 17, 2018 at 10:36

slaakso

9,1452 gold badges19 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

mysql join get column value from comma separated value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related