A SQL join with four tables.

• sepich.eric

  Say Hey Kid

  Points: 683

  More actions

  November 29, 2012 at 9:21 pm

  #266821

  I have four tables:

  Person (Join on Field ID int)

  Personal (Join on Field ID int)

  Internal (Join on Field ID int)

  Department (Join on Field Person_ID int)

  I want to join (*) all fields from every table on the fields specified above to create one table (sometimes called a flat list). I'm hoping someone can give me a skeleton for this as I am a bit lost. Thanks in advance for any help. I've had the greatest luck with this forum it really seems to be one of the best out there.

  I should probably note that in the Field Person_ID the same int ID like 1 or 0 can be listed multiple times.

• sepich.eric

  Say Hey Kid

  Points: 683

  More actions

  November 30, 2012 at 12:00 am

  #1564171

  insert into flatlist(Name,Department_Text,StartDate,Active,EndDate,Title,Manager,Phone,Zip4,Zip5,ZipC,State,City,Address,DOB,Gender) SELECT Person.Name, Department.Department_Text, Internal.StartDate, Internal.Active,Internal.EndDate, Internal.Title, Internal.Manager, Personal.Phone, Personal.Zip4, Personal.Zip5, Personal.ZipC, Personal.State, Personal.City, Personal.Address, Personal.DOB, Personal.Gender FROM Department INNER JOIN Personal INNER JOIN Person ON Personal.PersonID = Person.ID INNER JOIN Internal ON Person.ID = Internal.Person_Id ON Department.Internal_Id = Internal.Internal_Id

• Andrew Kernodle

  SSCertifiable

  Points: 5314

  More actions

  November 30, 2012 at 8:27 am

  #1564410

  Hm. Table definition details aside, that query looks correct with the exception of the JOINs in your FROM clause:

  FROM Department

  INNER JOIN Personal

  INNER JOIN Person ON Personal.PersonID = Person.ID

  INNER JOIN Internal ON Person.ID = Internal.Person_Id

  ON Department.Internal_Id = Internal.Internal_Id

  That should probably be more like this:

  FROM Department

  INNER JOIN Personal ON Department.Person_ID = Personal.PersonID

  INNER JOIN Person ON Personal.PersonID = Person.ID

  INNER JOIN Internal ON Person.ID = Internal.Person_Id

  -- AND Department.Internal_Id = Internal.Internal_Id

  You were missing JOIN criteria for your first join, and I'm not sure about the last ON in your original statement; if it's supposed to be an additional JOIN criteria, just uncomment it. I think that should be what you need here

  -