SQL join multiple tables

[danwednesday]

Hi,

I'm having real difficulty with a large database. The database contains a table of automobile parts made by several different companies for different automobile manufacturers. As a result I've decided to normalise the data into one main Products table with several other tables (such as product manufacturers etc) linking to this table.

I have the following tables:

tblPartManufacturer:
Id (Primary Key) / PartManufacturer

tblPartType:
Id (PK) / PartType

tblVehicleManufacturer:
Id (PK) / VehicleManufacturer

tblVehicleModel:
Id (PK) / VehicleModel

tblVehicleType:
Id (PK) / VehicleType

tblProduct:
Id (PK) / PartManufacturerId (FK) / PartTypeId (FK) / VehicleManufacturerId (FK) / VehicleModelId (FK) / VehicleTypeId (FK)


My trouble is now, I have no idea how to join these tables together to return a list of all the products with their full descriptions.

If anyone could point me in the right direction, it'd be much appreciated, thanks...

[Austin_Hastings]

Dan,

A couple of points:

1- Beware of treating everything as unique. Eventually you'll find that there a "Product" called 'oil filter for 1990 Chevette' that has several different vendor parts corresponding to it. (This is a configuration management issue, not a database issue, but your database needs to handle it.)

2- You haven't specified the fields you want.

What you want is an INNER JOIN. See these pages for help:
http://sqlcourse2.com/joins.html
http://www.devguru.com/features/tuto...vancedSQL.html


=Austin

[danwednesday]

Thanks Austin,

Each product has a distinct description, along with numerous other pieces of data identifying the product, so duplication in the future isn't going to be an option I'm sure (if two products are identical then they will be the same product). So this can be taken out of the problem, thanks anyhow.

I'm aware of INNER JOINs to an extent. At the moment I'm trying to make it work with just three tables (in the hope that I can then extend it to all six when I know it's possible). I've done the following, but it doesn't work:

SELECT tblPartType.*, tblVehicleType.* FROM tblProducts
INNER JOIN tblPartType ON tblProducts.PartType_Id = tblPartType.Id 
JOIN tblVehicleType ON tblProducts.VehicleType_Id = tblVehicleType.Id

I just get a "Syntax error (missing operator)" error.

Can anyone see what might be wrong with this?

[Austin_Hastings]

What SQL / DB are you using? Should the second JOIN be an INNER JOIN?

[danwednesday]

I'm using ASP to generate the SQL query for a MS Access database. INNER JOINing the second join doesn't make any difference, i.e. same error.

[r937]

access not only wants you to specify the keyword INNER, it also insists that you parenthesize your joins:

select tblPartType.*
     , tblVehicleType.* 
  from (
       tblProducts
inner 
  join tblPartType 
    on tblProducts.PartType_Id 
     = tblPartType.Id 
       )
inner
  join tblVehicleType 
    on tblProducts.VehicleType_Id 
     = tblVehicleType.Id

[danwednesday]

Okay, thanks r937 - I think I'm slowly getting there!

I have built up the following:

strQuery = "SELECT A.*, "
strQuery = strQuery & "B.*, "
strQuery = strQuery & "C.* "
strQuery = strQuery & "D.*, "
strQuery = strQuery & "E.*, "
strQuery = strQuery & "F.* "
strQuery = strQuery & "FROM tblProducts As A "
strQuery = strQuery & "INNER JOIN tblPartManufacturer As B On (A.PartManufacturer_Id = B.Id) "
strQuery = strQuery & "INNER JOIN tblPartType As C On (A.PartType_Id = C.Id) "
strQuery = strQuery & "INNER JOIN tblVehicleManufacturer As D On (A.VehicleManufacturer_Id = D.Id) "
strQuery = strQuery & "INNER JOIN tblVehicleModel As E On (A.VehicleModel_Id = E.Id) "
strQuery = strQuery & "INNER JOIN tblVehicleType As F On (A.VehicleType_Id = F.Id)"

I can't seem to get the parentheses in the right place (eveything I try just ends up with the same error). Do they have to be nested parenthsesis? This is much appreciated, by the way!

[jimfraser]

strQuery = "SELECT A.*, " & _
  "B.*, " & _
  "C.* " & _
  "D.*, " & _
  "E.*, " & _
  "F.* " & _
  "FROM (((((tblProducts A " & _
  "INNER JOIN tblPartManufacturer B ON (A.PartManufacturer_Id = B.Id)) " & _
  "INNER JOIN tblPartType C ON (A.PartType_Id = C.Id)) " & _
  "INNER JOIN tblVehicleManufacturer D ON (A.VehicleManufacturer_Id = D.Id)) " & _
  "INNER JOIN tblVehicleModel E ON (A.VehicleModel_Id = E.Id)) " & _
  "INNER JOIN tblVehicleType F ON (A.VehicleType_Id = F.Id))"

What I did
- got rid of AS to assign an alias to a table (not totally sure if this is bad, or just unneeded)
- put the parentheses in the right place for the joins to work
- in ASP you can use & _ to join lines, it's easier to code than var = var & "blah" every line

You might run into problems if you try to access a field that has a name common among your tables, like Id. I'm unsure as to whether you can get around this other than explicitly specifying the fields and assigning them aliases, like:
SELECT A.Id AS ProductId, B.Id AS PartMfrId,...
etc

[r937]

watch out, there's a comma missing after C.*

[jimfraser]

Hehe so there is, can you tell I didn't test it?

[danwednesday]

That works fine, thanks everybody - much appreciated.

One thing I did think, was that it might be useful to link the VehicleManufacturer and VehicleModel tables, like:

tblVehicleManufacturer:
Id (PK) / VehicleManufacturer

tblVehicleModel:
Id (PK) / VehicleManufacturerId (FK) / VehicleModel


Then in the main tblProducts table, get rid of the VehicleManufacturerId column, and just pass in the PK of the tblVehicleModel table, from which the manufacturer and model will be able to be returned to my recordset.

Is this possible within this Access SQL, and if so, I'm assuming it will need to be a different kind of JOIN?? My SQL is not very good as you've guessed, but I'm learning loads here which is much appreciated!

[jimfraser]

Sure it's possible, makes more sense too.
Be sure your table changes aren't going to screw up many many things before you make em =)

strQuery = "SELECT A.*, " & _
  "B.*, " & _
  "C.*, " & _
  "D.*, " & _
  "E.*, " & _
  "F.* " & _
  "FROM (((((tblProducts A " & _
  "INNER JOIN tblPartManufacturer B ON (A.PartManufacturer_Id = B.Id)) " & _
  "INNER JOIN tblPartType C ON (A.PartType_Id = C.Id)) " & _
  "INNER JOIN tblVehicleModel E ON (A.VehicleModel_Id = E.Id)) " & _
  "INNER JOIN tblVehicleManufacturer D ON (E.VehicleManufacturer_Id = D.Id)) " & _
  "INNER JOIN tblVehicleType F ON (A.VehicleType_Id = F.Id))"

Maybe your VehicleType_Id should be in tblVehicleModel also?

[danwednesday]

Thanks Jim,

I've just noticed that there are problems with the original selection (without joining the VehicleManufacturer and VehicleModel tables as in your last post).

It seems that not all records are being returned from the database. It's only returning those records which have a value for all fields. In fact some of the products don't have a particular VehicleManufacturer for example, assigned to them. In fact most of the products only have either a VehicleManufacturer/Model or a PartManufacturer value and not both.

I've tried LEFT JOIN instead of INNER JOIN, and although this returns more records, it doesn't return them all. I can't work out which ones it is and isn't returning!

Hypothetically, could I return all products - even if they don't have any foreign key values?

[jimfraser]

Depending on how your database works, which tables have a one-to-many relationship with others, which have a one-to-one and which have a (one or zero) to one, you might construct your query a number of ways.

For example, does each item in the Product table fit on only one vehicle? Or does it fit on many, or maybe zero vehicles? The answers to this will decide your join types, and whether you should add a table linking products and vehicles.

Each vehiclemodel I assume has 1 manufacturer, so unless I'm missing something you should move vehiclemanufacturerId from products to vehiclemodels.

Each vehicle has exactly one vehicletype I assume, so again move vehicletypeid from products to vehiclemodels.

You might want to draw a schema of how the tables relate to keep it clear.