Post reply

Table join question -- multiple records in one table

  • November 25, 2019 at 9:45 pm

    Go to Answer

    #3700790

    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

     

     

  • November 26, 2019 at 12:10 pm

    Answer

    #3701022

    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

  • November 26, 2019 at 8:11 pm

    #3701218

    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