Table join question -- multiple records in one table

  • 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?

    • This reply was modified 1 year ago by  ktflash.

    I want to be the very best
    Like no one ever was

  • 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