How I make this kind of Select in MySql?

Question

I have a table and it looks like this,

mysql> select * from match_info;
+---------------+---------+------------+-------------+-----------+
| match_info_id | user_id | body_types | hair_colors | ethnicity |
+---------------+---------+------------+-------------+-----------+
|             1 |       1 | 1,5,9,13   | 1,5,9,13    | 2,6,10    |
|             2 |       2 | 1,5,9,13   | 5           | 1,5,9     |
+---------------+---------+------------+-------------+-----------+

I have used lookup tables for body_types, hair_colors and ethnicity with id and name columns in each table.

Using above I need to select all values for particular user. Like this.

From body_type table.

Body Type: Skinny, Muscular, Large, Ripped

Hair Color: Blonde, Dark Brown, Strawberry Blonde, Dark Blonde etc....

Can anybody tell me how I make a select query to get result as above.

Hope somebody may help me out.

Thank you.

Your database design would be easier to handle in standard ways if you normalized it by including many-to-many join tables between the `body_types`, `hair_colors`, and `ethnicity` and the user. — Nathan Tuggy, Jun 07 '15 at 01:11
@NathanTuggy, Is there a way to get a result as above instead of changing database design? Thank you. — MCC, Jun 07 '15 at 01:21
Almost certainly. But fighting a relational database to avoid normalization tends to make things harder in a lot of ways. So unless you have serious, *specifically-identifiable and benchmarked* performance reasons for doing it this way, you should probably reconsider your design. — Nathan Tuggy, Jun 07 '15 at 01:22
@NathanTuggy, Actually I am not sure how to change my table design. Can you kindly help me? Thank you... — MCC, Jun 07 '15 at 01:28
I would recommend looking up resources on the keywords I mentioned: normalization and many-to-many/join tables. It's a fairly broad field, but there's a lot of good guidance out there, because it's crucial for almost any database design to understand normalization (and when to denormalize). — Nathan Tuggy, Jun 07 '15 at 01:32

Hotdin Gurning · Accepted Answer · 2015-06-07T03:26:17.093

You can join the tables and put find_in_set function at on clause. Try this :

select t.match_info_id, t.user_id,
    group_concat(distinct a.name) as body_types,
    group_concat(distinct b.name) as hair_colors,
    group_concat(distinct c.name) as ethnicity
from match_info as t
    inner join body_type as a on find_in_set(a.id,t.body_types)
    inner join hair_color as b on find_in_set(b.id,t.hair_colors)
    inner join ethnicity as c on find_in_set(c.id,t.ethnicity)
group by t.match_info_id, t.user_id

Please note that you can use left join instead of inner join depending on your needs.

How I make this kind of Select in MySql?

1 Answers1