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
SELECT
A.UserID,
A.ResumeFileName,
A.AppDatetimeEnd,
A.FirstName,
A.LastName,
A.ViewApp,
A.ViewReport,
A.ViewResume,
A.Score,
J.JobTitle,
J.TestType,
F.FolderName,
B.Dependability_JobFit,
Z.ZoneName
FROM
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
WHERE
A.Completed = 'C'
AND
A.AppDateTimeEnd >= DATEADD(dd,@TimeFrame,@date)
AND
A.FolderID IN(2,8,17)
AND
A.ZipCode = @ZipCode
AND
J.JobType = 1
Since you are not using any anything from the the Locations table L in your select , what happens if you run this query with a distinct?
I want to be the very best
Like no one ever was
November 26, 2019 at 8:11 pm
Thank you. I don't know why I did not try that before posting. Evidently I was making it out to be a lot more complicated.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply