Condition based MySQL left join with CASE

Happy to write post after long time 🙂

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`.

Below is the query what i have used to achieve my scenario and its works perfectly. I used mysql `case` and `LEFT JOIN` for pulling the required datas.

SELECT a.*, (
CASE a.group
WHEN 'user' THEN CONCAT(u.username)
WHEN 'deal' THEN d.deal_name
WHEN 'scene' THEN s.scene_title
ELSE ''
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

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: