Prefiltering data on an outer join

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)

Prefiltering data on an outer join

Author

Topic

KMW
Starting Member

2 Posts

Posted - 2005-01-07 : 23:48:26

I have a problem that I was able to solve in MS Access but am not sure how to in SQL Server:

I have 2 tables: People/Registration. Each person can register for 0 or more courses. I want to show the names of all people and whether they have registered for a particular course.

People columns = PKey, Name
Registration columns = RegKey, Pkey, CourseNo

In Access I built these queries:
qCourse = Select * from Registration where CourseNo=[C]

qPeopleCourses = SELECT Name,CourseNo FROM People LEFT JOIN qCourse ON People.Pkey= qCourse.PKey.

I called qPeopleCourses from vb and passed the parameter to this query and it gave me the data I was looking for, ie:
[C]=Course 001

Joe Course 001
Jon Course 001
Moe <null>

The registration table needs to be filtered before it is joined with the People table. In Access I can use the parameter against the 1st query but I am not sure how to do this in SQL Server.

Any ideas would be greatly appreciated.
Thanks
Kevin

Kristen
Test

22859 Posts

Posted - 2005-01-08 : 06:45:38

"I have 2 tables: People/Registration"

I presume you mean "People/Course" - os is there a third table?

If you want a list of ALL people, but only the Course data IF it is Course="XXX" then:


SELECT Name, CourseNo
FROM   People 
       LEFT JOIN qCourse 
            ON People.Pkey = qCourse.PKey
           AND qCourse.CourseNo = "XXX"

should do the job

Kristen

KMW
Starting Member

2 Posts

Posted - 2005-01-08 : 11:30:48

That worked
solution was all in the join.
Thanks
Kevin

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources