Joining 6 tables (1 Viewer)

[danb]

Hi,

Maybe someone can help. I'm struggling to find resources to help me join 6 tables through a SQL query to run on an ASP page.

There are five tables which are linked together by one table (tblProduct):

tblPartManufacturer:
Id (PK) / 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)


If anyone could help with this, or point me in the right direction I'd be most grateful.

Thanks.

 

[danb]

I'm trying to build it up, presuming that if I can join three tables, then six should be possible too.

The following doesn't even seem to work:

SELECT tblPartType.*, tblVehicleType.* FROM tblProducts 
JOIN tblPartType ON tblProducts.PartTypeId = tblPartType.Id
JOIN tblVehicleType ON tblProducts.VehicleTypeId = tblVehicleType.Id

Is any of this even possible?

 

[Pat Hartman]

If you are unfamiliar with SQL syntax, the easiest thing to do is to use the query builder. Create a new query. Add the product table and then add the other 5 tables. Draw a join line from the product table to each of the other tables, connecting the appropriate fields. Select the columns you want from each table. Save the query. Switch to SQL view and copy the generated SQL statement to paste into your ASP page.

BTW, it would be better to use "real" names for the ID fields rather than using the same name in every table. That way your joins will also make more sense since they will be joining like column names. For example tblProducts.PartTypeId = tblPartType.PartTypeId

 

[danb]

Thanks Pat,

I've got a semi-working solution now:

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))"

The problem is that this only retuns those products which have a foreign key link to another table (which is what an INNER JOIN is supposed to do I think). However, I need to be able to return every single product in the tlbProducts table regardless of whether or not they contain keys from another table. Practically speaking, not all products will be for a specific VehicleManufacturer, so this field may be empty for this product, but the product still needs to be available to view.

I've tried LEFT JOIN instead of INNER JOIN - which returns more records, but still not all (and I can't seem to fathom which it does and doesn't return!?)

Does anyone know how this might be done so I can retrieve a full product list - regardless of whether or not it has joining data? Thanks very much.

 

[Pat Hartman]

Changing all the Inner Joins to Left Joins should solve the problem.