MySQL secondary tables by table type reference -

i have table of 'posts' contains post_type , primary key of corresponding table, whereby each post_type there corresponding table.

i retrieve data corresponding tables if populate entries in social feed style wall. e.g. final data aught able packaged json entities.

here screenshots illustrate database tables:

`

posts

`

games

`

achievements

`

videos

i'm new mysql queries , have couple of considerations, wondering if possible using case statement of mysql. have added snippet of pseudo code illustrates little have in mind.

select * posts             case                  when posts.post_type = 'game'                      inner join games on (games.game_id = posts.origin_id)                 when posts.post_type = 'achievement'                     inner join achievements on (achievements.achievement_id = posts.origin_id)                 when posts.post_type = 'event'                     inner join events on (event.event_id = posts.origin_id)             end; 

alternatively if not possible, efficient or practical, appreciate more efficient alternative approach. created sqlfiddle sample database tables etc (not 100% accurate, testing).

an option have been told using left joins , experimenting them here:

http://sqlfiddle.com/#!9/d7fdde/2

however have not been able entity data using php without data loss / corruption. it's clear there missing entity data or additional data such missing created_at , updated_at entries , addition 1000 likes entry in 2nd entity.

https://gist.github.com/pluginio/ec9c411f75859570a087c53ca4671f3e

i tried remove null values left joins following php routines:

https://gist.github.com/pluginio/2444d2fb1098ebb3248f4fb84751d831

last not least, commenter below offered alternative approach uses inner joins , unions have been unable working:

http://sqlfiddle.com/#!9/d7fdde/12

hopefully quite clear result hoping sequential set of post entities own relevant table data in json formatted list.

regards

use sub query instead of join. don't use subquery if subquery table return more 1 row specific id.

check sql fiddle: click here

  select * ,case                      when posts.post_type = 'game'                          ( select games.name games games.game_id = posts.origin_id)                     when posts.post_type = 'achievement'                         ( select achievements.name achievements  achievements.achievement_id = posts.origin_id)                        end value_name posts; 

hope query helpful you.