SP inner join question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SP inner join question

Author

Topic

mickyjtwin
Starting Member

12 Posts

Posted - 2007-03-18 : 21:13:34

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

mickyjtwin
Starting Member

12 Posts

Posted - 2007-03-18 : 21:53:53

I figured it out...

SELECT LocationID, Title FROM tblLocations
WHERE LocationID IN (SELECT LocationID FROM tblContacts WHERE SpecialityID = @SpecialityID)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-18 : 21:59:22

or use INNER JOIN


select l.LocationID, l.Title
from       tblLocations l
inner join tblContacts c
on     l.LocationID   = c.LocationID
where  c.SpecialityID = @SpecialityID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources