I had a scenario that there is are some fields in a table like `id`,`group`,`object_id`.
`group` field(ENUM) has three groups named `user`, `deal`, `scene` and `object_id`(it will have the primary key for corresponding group).
ie., If the row has group name of `user`, then `object_id` refers to `user_id` and if the row has group name of `deal`, then object id will refers to `deal_id`.
What i want to achieve is i need to have a field called `name` in result so that if the current row group is user then username should be displayed in `name` field and if the row group is `deal` then the `name` field should have `deal_name`.
SELECT a.*, (
WHEN 'user' THEN CONCAT(u.username)
WHEN 'deal' THEN d.deal_name
WHEN 'scene' THEN s.scene_title
END) AS name
FROM `activity_logs` as `a`
LEFT JOIN `users` as `u` on `a`.`group` = 'user' AND `a`.`object_id` = u.id
LEFT JOIN `deals` as `d` on `a`.`group` = 'deal' AND `a`.`object_id` = d.id
LEFT JOIN `scenes` as `s` on `a`.`group` = 'scene' AND `a`.`object_id` = s.id