Rutger Huijsmans Rutger Huijsmans - 10 months ago 52
MySQL Question

Find out with which column the value of another column matches

I've the following query:

SELECT g.group_player1_user_id, g.group_player2_user_id, g.group_player3_user_id, g.group_player4_user_id, r.runk_user_id
FROM groups g
JOIN runks r
ON g.group_id = r.runk_group_id

This will give the following output:

enter image description here

I need to know if my 'runk' is belonging to player 1, 2, 3 or 4.
How do I find out what column matches my runk_user_id?

(I am aware not all user_ids will match but that's because I've been playing around with my data. This will not happen in the production version)

Answer Source

You'll have to test all them. You can accomplish this in a fairly elegant way with a case expression:

SELECT r.runk_user_id,
       CASE r.runk_user_id WHEN g.group_player1_user_id THEN 'player 1'
                           WHEN g.group_player2_user_id THEN 'player 2'
                           WHEN g.group_player3_user_id THEN 'player 3'
                           WHEN g.group_player4_user_id THEN 'player 4'
                           ELSE 'no player matches'
       END AS player
FROM   groups g
JOIN   runks r ON g.group_id = r.runk_group_id