Gary Paluk Gary Paluk - 1 year ago 116
MySQL Question

MySQL secondary tables by table type reference

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:

`

Posts



Posts

`

Games



Games

`

Achievements



Achievements

`

Videos



Videos

I'm new to MySQL queries and so I have a couple of considerations, I am wondering if this is possible using the CASE statement of MySQL. I have added a snippet of PSEUDO CODE that hopefully illustrates a little what I have in mind.

SELECT * FROM posts
CASE
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)
END;


Alternatively if this is NOT possible, efficient or practical, then I would really appreciate an more efficient alternative approach. I created an SQLFiddle with some sample database tables etc (NOT 100% accurate, just for testing).

An option I have been told about is using LEFT JOINS and I am experimenting with them here:

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

However I have not been able to effectively get the entity data using PHP without data loss / corruption. It's clear that there is missing entity data or even additional data such as missing created_at and updated_at entries and addition 1000 likes entry in the 2nd entity.

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

I tried to remove the NULL values from the LEFT JOINS with the following PHP routines:

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

Last but not least, a commenter below offered an alternative approach that uses INNER JOINS and UNIONS however I have been unable to get that working:

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

Hopefully it is quite clear that the result that I am hoping for is a sequential set of post entities with its own relevant table data in a JSON formatted list.

Regards

Answer Source

Use sub query instead of the join. Don't use subquery if subquery table return more than one row for specific id.

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download