mysql query to search multiple values in comma separated list

Question

I have a table Orders:

Order VARCHAR(20)

Developer VARCHAR(50)

I need to SELECT field in the following way:

For example I have this data in my Table:

Order              Developer

Order 141          Tim (Apple), Alex (Microsoft), Sara (Amazon), Neylo
Order 171          James (Apple), John (Amazon)
Order 181          Nike (Microsoft)

Need to make SQL query to get this:

Developer                Order

Tim, James (Apple)       Order 141, Order 171
Alex, Nike (Microsoft)   Order 141, Order 181
Sara, John (Amazon)      Order 141, Order 171
Neylo                    Order 141

Is this possible to make? Any idea is welcome

Answer 1

if this is your database then it is a mess. A golden rule is to have all data only one time in your database. Therefor you should create a table "user" that have all users in his:

user:

• user_id
• name
• email
• ...

Then you could make your order table like this:

order:

• order_id
• user_id
• ...

Now you can get all information like this:

SELECT order.order_id, user.name FROM orders
JOIN users ON order.user_id=user.user_id
WHERE order.order_id=141

Understanding JOINS in MySQL

Answer 2

The best way would be to use a new table to make it atomic like others mentioned, but it sounds like you can't do that.

If you can add a stored function this solution will work. http://blog.fedecarg.com/2009/02/22/mysql-split-string-function/

If you cannot add a function (e.g. hosted with no admin acces, etc). You could break the data apart using substrings (ugly ugly ugly -- I even hate to mention it it's so bad -- but it could work if you write for the maximum number of supported values). https://stackoverflow.com/a/11008567/455627

e.g.

 substring_index(`Developer`,',',1) ==> first value
 substring_index(substring_index(`Developer`,',',-2),',',1) ==> second value

etc.