Same as Jeff, I'm not sure what do you mean by employee having several EmpId's, however, your current query
SELECT Distinct a.*,
FROM #NoStaffID as a
Left JOIN #Station b
On a.City = b.City
JOIN Employee.Payroll c
WHERE c.PayID is not null
will only return records where PayID is present and not null.
I have one question and one suggetsion to you:
1. Why there is no join on EmpID to Payroll table? Surely you may have multiple Payroll records per city, looks like "cut & paste" issue to me.
2. If you follow the order of JOINS from INNER to OUTER and use a bit better formatting, you will make your query much more readable and therefore more maintainable:
SELECT DISTINCT a.*
FROM #NoStaffID AS a
INNER JOIN Employee.Payroll AS c
ON c.City = a.City
LEFT JOIN #Station AS b
ON b.City = a.City
WHERE c.PayID IS NOT NULL
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)How to post your question to get the best and quick help