Selecting only certain records

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 2000 Forums

SQL Server Development (2000)

Selecting only certain records

Author

Topic

Mannga
Yak Posting Veteran

70 Posts

Posted - 2001-11-15 : 06:38:54

Hi All,

I would have thought this is an easy proc to write but I am having major hassles here.

What I have is a linking table which has the following data.

StaffID ¦ ClinicID

What I am trying to do is find out all the Distinct ClinicID which don't have a matching StaffID.

So if the database goes something like this.

StaffID ¦ ClinicID
2        1
2        3
3        1
4        2

So now I I run the query with staffID of 2 I want it to return '2' since that is the only clinicID that it doesn't match with. The problem I keep on getting is that it is returning '1' and '2' because '1' is also linked with staffID '3'.

I hope I made sense with that.

Please could someone tell me what I am doing wrong. I have the following.

SELECT DISTINCT(pc_ClinicID), pc_ClinicName FROM sbt_ParticipatingClinics
INNER JOIN ssbt_ClinicUsers ON sbt_ParticipatingClinics.pc_ClinicID = ssbt_ClinicUsers.cu_ClinicID
WHERE ssbt_ClinicUsers.cu_ClinicStaffID != @StaffID
Order By pc_ClinicID

?????

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources