MySQL: How to pull information from multiple tables based on information in other tables?

Question

Ok, I have 5 tables which I need to pull information from based on one variable.

gameinfo
id | name | platforminfoid

gamerinfo
id | name | contact | tag

platforminfo
id | name | abbreviation

rosterinfo
id | name | gameinfoid

rosters
id | gamerinfoid | rosterinfoid

The 1 variable would be gamerinfo.id, which would then pull all relevant data from gamerinfo, which would pull all relevant data from rosters, which would pull all relevant data from rosterinfo, which would pull all relevant data from gameinfo, which would then pull all relevant data from platforminfo.

Basically it breaks down like this:

• gamerinfo contains the gamers basic information.
• rosterinfo contains basic information about the rosters (ie name and the game the roster is aimed towards)
• rosters contains the actual link from the gamer to the different rosters (gamers can be on multiple rosters)
• gameinfo contains basic information about the games (ie name and platform)
• platform info contains information about the different platforms the games are played on (it is possible for a game to be played on multiple platforms)

I am pretty new to SQL queries involving JOINs and UNIONs and such, usually I would just break it up into multiple queries but I thought there has to be a better way, so after looking around the net, I couldn't find (or maybe I just couldn't understand what I was looking at) what I was looking for. If anyone can point me in the right direction I would be most grateful.

Answer 1

There is nothing wrong with querying the required data step-by-step. If you use JOINs in your SQL over 5 tables, we sure to have useful indexes on all important columns. Also, this could create a lot of duplicate data:

Imagine this: You need 1 record from gamerinfo, maybe 3 of gameinfo, 4 ouf of rosters and both 3 out of the remaining two tables. This would give you a result of 1*3*4*3*3 = 108 records, which will look like this:

ID    Col2    Col3
1     1       1
1     1       2
1     1       3
1     2       1
...   ...     ...

You can see that you would fetch the ID 108 times, even if you only need it once. So my advice would be to stick with mostly single, simple queries to get the data you need.

Answer 2

There is no need for UNION just multiple JOINs should do the work

SELECT gameinfo.id AS g_id, gameinfo.name AS g_name, platforminfoid.name AS p_name, platforminfoid.abbreviation AS p_abb, rosterinfo.name AS r_name
FROM gameinfo
LEFT JOIN platforminfo ON gameinfo.platforminfoid = platforminfo.id
LEFT JOIN rosters ON rosters.gameinfoid = gameinfo.id
LEFT JOIN rosterinfo ON rosterinfo.id = rosters.rosterinfoid

WHERE gameinfo.id = XXXX

this should pull all info about game based on game id indexing on all id(s) gameinfoid, platformid, rosterinfoid will help on performance