Joining More Than Two Tables-Lists

[Berkey]

I keep searching for an example or explanation of how you join more than two tables. I know this has to be covered, but I can't find anything that I can clearly understand (or figure out if it relates to what I am trying to do: The tutorials cover linking two tables and I have achieved this functionality, but three or more tables escapes me).

Here is an example of what I am trying to accomplish. List of manufactures names, list of models names with a join to manufacturers (I have this working), list of items that has both model names and serial numbers. I want to be able to add serial number records to the items table, select a model, and have the manufacture selected and saved as well.

A note: Serial numbers stored in the items table may not necessarily be unique within the table (but they need to be unique to a model). They would be unique if you reference the model name. What I mean here is that two different manufacturers may have items with the same serial number, but a manufacturer wouldn't have two model names with items having the same serial number. Crossing fingers for now that we don't find two manufactures who use the same model name (really hoping this doesn't nightmare doesn't happen).

I know these tasks have to be much easier to achieve than my brain is making them out to be. Please help me see clearly if you can.

 

[genyded]

Hi,

What you probably want is a couple of 'mapping' tables that are just id links between 2 other tables (similar to Joomla's users and user groups in the user_usergroups_mapping table. Once you have those, you can join to as many other tables as needed and control whether values must be unique and can have one-to-many relationships.

So if I got what you said correct.... map items to model names in one map table and items to serial numbers in another, Then the look-ups can be based on selecting WHERE IN those tables whether there are one or many. Hope that makes at least some sense. It's hard to be more specific without knowing your columns and such.

Dale