SQL Four Tables One Recordset

Question

What am I missing? I need to return all records that match the Last_Name Query whether they do or do not have a Customer Number in the Field_Table.

I am calling the Client table twice because each client Id has a parent whose contact number = 0. I need the parent record to return the city, state, zip and company name.

I have tried looping with recordset.movenext but the query becomes extremely slow which makes looping undesirable.

How can I query the Client table to include the Customer Number when it's available and return when it's missing?

Current results are records that match the Last_Name query and DO have a customer_number. I do not get any records that match the last_name query but DO NOT have a customer number.

Note: If the Company does not have a Number then the company does not have a record in the Field_Table.

SELECT A.Contact, 
       A.Id, 
       A.First_Name, 
       A.Last_Name, 
       B.Company_Name, 
       B.City, 
       B.State, 
       FT.Number
FROM Client C 
     INNER JOIN Client B ON A.Id = B.Id 
     LEFT OUTER JOIN Field_Table FT ON B.Id = FT.Id 
     LEFT OUTER JOIN Field_Definitions FD ON FT.Type_Id = FD.Type_Id
WHERE (A.Last_Name LIKE '%Last Name%') 
  AND (B.Contact = 0) 
  AND (FD.Description = 'Customer Number')

Answer 1

You need to put the FD.Description part in the ON join condition, not in the WHERE clause.

SELECT A.Contact, A.Id, A.First_Name, A.Last_Name, B.Company_Name, B.City, B.State, FT.Number
FROM Client C
INNER JOIN Client B ON A.Id = B.Id
LEFT OUTER JOIN Field_Table FT ON B.Id = FT.Id
LEFT OUTER JOIN Field_Definitions FD ON (FT.Type_Id = FD.Type_Id) AND (FD.Description = 'Customer Number')
WHERE (A.Last_Name LIKE '%Last Name%') AND (B.Contact = 0)

Answer 2

SELECT A.Contact, 
       A.Id, 
       A.First_Name, 
       A.Last_Name, 
       B.Company_Name, 
       B.City, 
       B.State, 
       FT.Number
FROM   Client C 
INNER JOIN
       Client B
ON     B.Id = A.id
       AND B.contact = 0
INNER JOIN
       Field_Definitions FD
ON     FD.Description = 'Customer Number'
LEFT OUTER JOIN
       Field_Table FT
ON     FT.Type_Id = FD.Type_Id
       AND FT.id = B.Id
WHERE  A.Last_Name LIKE '%Last Name%'

Answer 3

Now do you understand why you can't put the reference to the table in the right side of the left join in the where clause? The reason is that putting it in the where clause you convert the join to an inner join since the where condition must be met by all records. Anytime you use a left join the only reference to it that will work and retain the join is one where you are looking for null records which then gives you the records inthe rest of the joins that are not inthe table referenced.