Search code examples
mysqlsqlwhere-clauseunpivot

MySQL select all column names where condition is met for specific row

I have a table which links users to groups which is set up as below: enter image description here

I would like to know if there is a method to select all groupIDs (column names) where a certain condition is met.

E.g. if I want to find all groups where user 2 has a level greater than 0 it would return (1,2,4)

It is also worth noting it cannot be done manually as there is roughly 5000 rows and 120 columns

Thanks for any help!

Solution

  • You can unpivot and search with union all. Assuming that the columns of your table are called grp1 to grp5:

    select 1 as grp from t where userid = 2 and grp1 > 0
union all select 2 from t where userid = 2 and grp2 > 0
union all select 3 from t where userid = 2 and grp3 > 0
union all select 4 from t where userid = 2 and grp4 > 0
union all select 5 from t where userid = 2 and grp5 > 0

    You should consider fixing your data model. Each user/group tuple should be stored in a separate row in a bridge table, like so:

    user_groups:

    userid    grp      val
     1      1        3
     1      2        2
     1      3        2
     1      4        2
     1      5        0
     ...

    Then, the query is as simple as:

    select grp from user_groups where userid = 2 and val > 0