November 29, 2012 at 9:21 pm
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.
November 30, 2012 at 12:00 am
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
November 30, 2012 at 8:27 am
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 🙂
- 😀
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply