need to return all records from one table and a few from the other 1

[jadams0173]

I'm trying to return all the customer from my customers table and only the data from the tbldata where the given employee id is in the criteria. That's the easy part for me.

The hard part is. If the employeed id doesn't exist in tblData at ALL I need to show all of the customers with blanks for the utilization and notes. The way it's written now it will only return the number of records that match the employee id or are null.

SELECT CUSTOMERS.BU, tblData.EmpUtlization, tblData.EmployeeID, tblData.Notes
FROM CUSTOMERS LEFT JOIN tblData ON CUSTOMERS.CustomerID = tblData.CustomerID
WHERE (((tblData.EmployeeID)=44 Or (tblData.EmployeeID) Is Null));

In this example employeeid is in tblData 1 time. However there are 10 other employeeid's in tblData so in stead of getting all 37 customers from the CUSTOMERS table with 36 blanks and one populated, I get 1 populated and 25 blanks.

Make sense?

 

[PHV]

What about this ?
SELECT C.BU, D.EmpUtlization, D.EmployeeID, D.Notes
FROM CUSTOMERS C LEFT JOIN (
SELECT EmployeeID, CustomerID, EmpUtlization, Notes
FROM tblData WHERE EmployeeID=44
) D ON C.CustomerID = D.CustomerID

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jadams0173]

Beautiful PHV. Thanks again!