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.

Answer 1

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.