This is an employment application database. I have a foreign key table named Application_Locations that has 2 columns UserID and LocationID. This is a many to many relationship
i recently had someone ask me to write a report that would produce results from a zip code search. They then asked me to list the zone(s) people applied to and display in the results. There are 5 zones. Each zone has upwards of 30 locations. I told them it would result in multiple records if the applicant applied to more than one location. Is there a way to only list a record for each zone instead of for each location?
here is the query from the sproc
dbo.Application A INNER JOIN
dbo.Jobs J on A.JobID = J.JobID INNER JOIN
dbo.ElectronicFolder F ON A.FolderID = F.FolderID LEFT OUTER JOIN
dbo.BestHireScores B ON A.UserID = B.UserID INNER JOIN
dbo.Application_Locations AL ON AL.UserID = A.UserID INNER JOIN
dbo.Locations L ON AL.LocationID = L.LocationID INNER JOIN
dbo.Zones Z ON L.ZoneID = Z.ZoneID
A.Completed = 'C'
A.AppDateTimeEnd >= DATEADD(dd,@TimeFrame,@date)
A.ZipCode = @ZipCode
J.JobType = 1