Websnippetz

Get cool snippets on php, css, jQuery, Magento and wordpress

Condition based MySQL left join with CASE

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

karthi s

Leave a Reply

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

%d bloggers like this: