SitePoint Sponsor

User Tag List

Results 1 to 14 of 14
  1. #1
    SitePoint Guru
    Join Date
    Jun 2004
    Location
    UK
    Posts
    605
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL join multiple tables

    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...

  2. #2
    SitePoint Evangelist
    Join Date
    May 2004
    Location
    New Jersey, USA
    Posts
    567
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  3. #3
    SitePoint Guru
    Join Date
    Jun 2004
    Location
    UK
    Posts
    605
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:

    Code:
    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?

  4. #4
    SitePoint Evangelist
    Join Date
    May 2004
    Location
    New Jersey, USA
    Posts
    567
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What SQL / DB are you using? Should the second JOIN be an INNER JOIN?

  5. #5
    SitePoint Guru
    Join Date
    Jun 2004
    Location
    UK
    Posts
    605
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    access not only wants you to specify the keyword INNER, it also insists that you parenthesize your joins:
    Code:
    select tblPartType.*
         , tblVehicleType.* 
      from (
           tblProducts
    inner 
      join tblPartType 
        on tblProducts.PartType_Id 
         = tblPartType.Id 
           )
    inner
      join tblVehicleType 
        on tblProducts.VehicleType_Id 
         = tblVehicleType.Id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Guru
    Join Date
    Jun 2004
    Location
    UK
    Posts
    605
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay, thanks r937 - I think I'm slowly getting there!

    I have built up the following:

    Code:
    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!

  8. #8
    SitePoint Wizard
    Join Date
    Nov 2004
    Location
    Nelson BC
    Posts
    2,310
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    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

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    watch out, there's a comma missing after C.*
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Wizard
    Join Date
    Nov 2004
    Location
    Nelson BC
    Posts
    2,310
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hehe so there is, can you tell I didn't test it?

  11. #11
    SitePoint Guru
    Join Date
    Jun 2004
    Location
    UK
    Posts
    605
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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!

  12. #12
    SitePoint Wizard
    Join Date
    Nov 2004
    Location
    Nelson BC
    Posts
    2,310
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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 =)
    Code:
    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?

  13. #13
    SitePoint Guru
    Join Date
    Jun 2004
    Location
    UK
    Posts
    605
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  14. #14
    SitePoint Wizard
    Join Date
    Nov 2004
    Location
    Nelson BC
    Posts
    2,310
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •