Out of about 6300 employees and 53 sites there are about around 200 that have addresses in multiple locations.
If I sort the list by ID and Site.ID the first matching result is the value we want returned, and ignore any other for that employee.
Any way to do that?
SELECT DISTINCT Employees.ID, Employees.FName, Employees.LName, Address.City, Address.State, Site.ID from Employees Left Join Address on Employees.ID = Address.ID Left Join Site on Address.ADID = Site.ID Where Site.ID in ( SELECT Site.ID From Site Where Site.Type = 'Primary' ) Order by Employees.ID, Site.ID
Example result set:
1234 John Williams Sacramento CA 1 1234 John Williams Portland OR 2 1111 Mary Johnson Sacramento CA 1 1112 James Stoller Carson City NV 2
Would like this back:
1234 John Williams Sacramento CA 1 1111 Mary Johnson Sacramento CA 1 1112 James Stoller Carson City NV 2