Trouble with SQL Query

• Michael Wingert-413688

  SSC Rookie

  Points: 32

  More actions

  March 18, 2007 at 7:20 pm

  #194789

  I 3 tables which hold locations, specialities and contacts.

  tblLocations

  LocationID

  Title

  tblSpecialities

  SpecialityID

  Title

  tblContact

  ContactID

  Name

  LocationID

  SpecialityID

  I am working with a serach directory that has a dropdown list for specialities. When a user selects a speciality, the location dropdown will update with locations found, i.e. assigned to a contact.

  CREATE PROCEDURE sp_GetLocationsBySpecialityID

  @SpecialityID int

  AS

  SELECT LocationID, Title FROM tblLocations

  ...

  Not sure how to implement this search!

  Thanks in advance,

  Mick

• MohammedU

  SSCoach

  Points: 19644

  More actions

  March 18, 2007 at 7:46 pm

  #695627

  Try the following...

  SELECT l.LocationID, l.Title FROM tblLocations l

  join tblContact c on l.LocationId = c.LocationId

  join tblSpecialities s on s.SpecialityId = c.SpecialityId

  where s.SpecialityID = @SpecialityID

   

   

  MohammedU
  Microsoft SQL Server MVP