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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy