Multiple Join

  • Thread starter Thread starter David Plotts
  • Start date Start date
D

David Plotts

I'm trying to join a few tables together. All the tables have relationships
based on Person_ID.

SELECT (Person_T.First_Name & " " & Person_T.Last_Name),
Person_T.Company_Name, Person_T.Title, Person_T.First_Name,
Person_T.Last_Name, Person_T.Company_Name,
Business_Physical_Address_T.BP_Street,
Business_Physical_Address_T.BP_Line_2, Business_Physical_Address_T.BP_City,
Business_Physical_Address_T.BP_State, Business_Physical_Address_T.BP_Zip,
Phone_Number_T.Business_Number, Phone_Number_T.Business_Number_Extensinon,
Phone_Number_T.Business_Number_2, Phone_Number_T.Business_Fax_Number,
Phone_Number_T.Cell_Number, Phone_Number_T.Pager_Number,
Person_T.Email_Address, Person_T.Web_Site
FROM Person_T, Business_Physical_Address_T, Phone_Number_T
WHERE Person_T.Person_ID=Business_Physical_Address.Person_ID And
Person_T.Person_ID=Phone_Number.Person_ID;

When I go to run it, it asks for parameter IDs.
 
David,

I'm not sure why it's asking for a parameter, but I see a minor discrepancy
in the last line of your SQL. I think that

WHERE Person_T.Person_ID=Business_Physical_Address.Person_ID And
Person_T.Person_ID=Phone_Number.Person_ID;

needs a "_T" in it, as in

WHERE Person_T.Person_ID=Business_Physical_Address.Person_ID And
Person_T.Person_ID="Phone_Number_T.Person_ID";

Sam
 
You are missing the "_T" in two places in the WHERE clause.

SELECT (Person_T.First_Name & " " & Person_T.Last_Name)
, Person_T.Company_Name
, Person_T.Title
, Person_T.First_Name
, Person_T.Last_Name
, Person_T.Company_Name
, Business_Physical_Address_T.BP_Street
, Business_Physical_Address_T.BP_Line_2
, Business_Physical_Address_T.BP_City
, Business_Physical_Address_T.BP_State
, Business_Physical_Address_T.BP_Zip
, Phone_Number_T.Business_Number
, Phone_Number_T.Business_Number_Extensinon
, Phone_Number_T.Business_Number_2
, Phone_Number_T.Business_Fax_Number
, Phone_Number_T.Cell_Number
, Phone_Number_T.Pager_Number
, Person_T.Email_Address
, Person_T.Web_Site
FROM Person_T, Business_Physical_Address_T, Phone_Number_T
WHERE Person_T.Person_ID=Business_Physical_Address_T.Person_ID And
Person_T.Person_ID=Phone_Number_T.Person_ID;

I would rather use table references and joins in the FROM clause to do this
query.

I would restate this SQL as
SELECT (P.First_Name & " " & P.Last_Name)
, P.Company_Name
, P.Title
, P.First_Name
, P.Last_Name
, P.Company_Name
, B.BP_Street
, B.BP_Line_2
, B.BP_City
, B.BP_State
, B.BP_Zip
, N.Business_Number
, N.Business_Number_Extensinon
, N.Business_Number_2
, N.Business_Fax_Number
, N.Cell_Number
, N.Pager_Number
, P.Email_Address
, P.Web_Site
FROM (Person_T As P INNER JOIN Business_Physical_Address_T as B
ON P.PersonID = B.PersonID) INNER JOIN Phone_Number_T as N
ON P.Person_ID = N.Person_ID;
 
Back
Top