I have a table of 'posts' that contains post_type and primary key of a corresponding table, whereby for each post_type there is a corresponding table.
I would like to retrieve the data from the corresponding tables as if to populate the entries in a social feed style wall. e.g. The final data aught to be able to be packaged up as JSON entities.
Here are some screenshots to illustrate my database tables:
SELECT * FROM posts
WHEN posts.post_type = 'game' THEN
INNER JOIN games ON (games.game_id = posts.origin_id)
WHEN posts.post_type = 'achievement' THEN
INNER JOIN achievements ON (achievements.achievement_id = posts.origin_id)
WHEN posts.post_type = 'event' THEN
INNER JOIN events ON (event.event_id = posts.origin_id)
sub query instead of the
join. Don't use
return more than one row for specific
Check this SQL Fiddle: Click Here
SELECT * ,CASE WHEN posts.post_type = 'game' THEN ( SELECT games.name from games where games.game_id = posts.origin_id) WHEN posts.post_type = 'achievement' THEN ( SELECT achievements.name FROM achievements WHERE achievements.achievement_id = posts.origin_id) END as value_name FROM posts;
Hope this query helpful to you.