Table join question -- multiple records in one table

• mjohnson 71479

  SSC Eights!

  Points: 848

  More actions

  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

   

   

  • This topic was modified 5 years, 7 months ago by Steve Jones - SSC Editor.

• ktflash

  Default port

  Points: 1400

  More actions

  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 5 years, 7 months ago by ktflash.

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

  https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help

• mjohnson 71479

  SSC Eights!

  Points: 848

  More actions

  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.