Is it possible to use indirection for choosing tables in SQL queries (MySQL specifically). I want to be able to determine what table I want to access based on values in another table. Though as I'm writing this it occurs to me that perhaps instead of creating a new table, I should merge the tables and add a column defining the specific type that the sub table would contain. I'm still curious about my question either way...

Basically what I'm doing is an inheritance type of approach where there is one table with all common data so all objects can be searched at once, then sub tables for more specific information. I think my problem was that when I had situations where I wasn't sure which sub table needed to be used, my architecture was bad.

The best thing is to either use just one whole table, or two subtables.

For example you can have a table "items" and another table "dealers" with multiple dealers per item (each dealer having his own details), so you for each item you may want to have a multiple row detailed table which is why you possibly want separate tables instead of just one. If that is the case, take a look at the JOIN syntax in MySQL, that allows you to link a row from one table, to selecting a few rows from another table.

It is actually impractical to have one sub-table per item in your main table, except if each of these sub tables will have tottally different fields (columns) which just can't go into one table. In that case, you might have to combine this with your server side scripting to return the results from one query, process the results (using basic control structures) and choose which table to work with next. I think MySQL 5 has features to get this done without resorting to other scripting, but the performance time that you will save will be negligible, and there will be a slight learning curve involved for it, so it's practical to go back to your server side script and take your next step.

Hmm, I thought I had this solved but it appears I was wrong.

What I have is three tables, Skills, SkillsHierarchy, Ammunition.

The sample skills table is:CharacterID | RangedSkill | LaserSkill76          | 750         | 500Where 76 is the PlayerID and 500 is 50% max skill and 750 is 75%.SkillsHierarchy table is:SkillParent | SkillNameRangedSkill | LaserSkillAmmunition table is:AmmoType | AmmoSkill1        | LaserSkillWhere 1 corresponds with Laser Beam.

What I'm trying for is when a player fires a weapon with AmmoType 1, it can be determined that the primary skill is LaserSkill and base skill is RangedSkill. For player 76 LaserSkill=750 and RangedSkill=500.

Is there a query that can get this or do I have to go a different route? Bear with me as I'm fairly new at MySQL though I'm getting pretty good as I can currently walk around the world, look at things, get things and drop things successfully.

[Edit] Oh, and I could easily do this with an if, else if block in my SQL functions but I'm trying to set this up so I don't have to update my function if I add new skills to the database.

<slaps forehead> I've been making this mistake since I started working in SQL. You're talking about a double relational table I think its called (or something similar). thanks you.

Excellent, just as I was shooting for:

mysql> select PrimaryCharacterSkill.SkillRating as PrimarySkillRating    ->    from CharacterSkills as PrimaryCharacterSkill    ->       join Ammunition    ->    where    ->       Ammunition.AmmoType=1 AND    ->       Ammunition.AmmoSkill=PrimaryCharacterSkill.SkillID ;+--------------------+| PrimarySkillRating |+--------------------+|                750 |+--------------------+1 row in set (0.00 sec)mysql>mysql> select BaseCharacterSkill.SkillRating as BaseSkillRating    ->    from CharacterSkills as BaseCharacterSkill    ->       join Skills as ParentSkill    ->       join Skills    ->       join Ammunition    ->    where    ->       Ammunition.AmmoType=1 AND    ->       Ammunition.AmmoSkill=Skills.SkillID AND    ->       Skills.SkillParentID=ParentSkill.SkillID AND    ->       BaseCharacterSkill.SkillID=ParentSkill.SkillID ;+-----------------+| BaseSkillRating |+-----------------+|             500 |+-----------------+1 row in set (0.00 sec)

Now if I make the decision to implement unlimited depth to skill hierarchy I'll have to look into recursive selects or something but thats for later if I ever decide to try that...