Need help with query that pulls Courses

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

Transact-SQL (2000)

Need help with query that pulls Courses

Author

Topic

j_mact
Starting Member

17 Posts

Posted - 2007-04-26 : 09:54:12

All,
I have two tables with course titles. I am trying to run a query based off of a studentid number, but the results keep repeating the course title from table a next to each course title from table b.

What I would like is to have the courses not repeat.

Here is the query:


select a.studentid, a.firstname, a.lastname, a.coursetitle as CurrentCourse, 
b.coursetitle as CourseSelection
from LastYearCourse a inner join courseselect b on a.studentid = b.studentid

Below is a sample of the results:
studentid firstname lastname CurrentCourse CourseSelection
12345 JANE ANYPERSON HONORS ENGLISH II ADVANCED PLACEMENT PSYCHOLOGY
12345 JANE ANYPERSON HONORS ENGLISH II MEDIA CENTER ASSISTANT
12345 JANE ANYPERSON HONORS ENGLISH II HN DISCRETE MATH/STATISTICS
12345 JANE ANYPERSON HONORS ENGLISH II HONORS ANATOMY AND PHYSIOLOGY
12345 JANE ANYPERSON HONORS ENGLISH II INTRO TO AP SOCIAL STUDIES
12345 JANE ANYPERSON HONORS ENGLISH II ADVANCED PLACEMENT ENGLISH III
12345 JANE ANYPERSON HONORS ENGLISH II HONORS LATIN IV
12345 JANE ANYPERSON HONORS ENGLISH II AP LATIN IV (LATIN:VERGIL)
12345 JANE ANYPERSON SPANISH I ADVANCED PLACEMENT PSYCHOLOGY
12345 JANE ANYPERSON SPANISH I MEDIA CENTER ASSISTANT
12345 JANE ANYPERSON SPANISH I HN DISCRETE MATH/STATISTICS
12345 JANE ANYPERSON SPANISH I HONORS ANATOMY AND PHYSIOLOGY

Any help would be appreciated.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-26 : 09:56:55

So what is the required result ?

j_mact
Starting Member

17 Posts

Posted - 2007-04-26 : 10:20:48

The required result would look like this:

studentid firstname lastname CurrentCourse CourseSelection
12345 JANE ANYPERSON HONORS ENGLISH II ADVANCED PLACEMENT PSYCHOLOGY
12345 JANE ANYPERSON SPANISH I MEDIA CENTER ASSISTANT
12345 JANE ANYPERSON HONORS LATIN III HN DISCRETE MATH/STATISTICS
12345 JANE ANYPERSON HONORS ALGEBRA II HONORS ANATOMY AND PHYSIOLOGY
12345 JANE ANYPERSON SPANISH II INTRO TO AP SOCIAL STUDIES
12345 JANE ANYPERSON HONORS CHEMISTRY I ADVANCED PLACEMENT ENGLISH III
12345 JANE ANYPERSON HONORS CIVICS AND ECONOMICS HONORS LATIN IV
12345 JANE ANYPERSON WORLD RELIGIONS AP LATIN IV (LATIN:VERGIL)

Sorry about the spacing. I am pasting this out of excel.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-26 : 10:26:16

"Sorry about the spacing. I am pasting this out of excel."
You can enclose the text in between the pair [Code][/Code] (in all lower or all upper case) to format it.


12345 JANE ANYPERSON HONORS ENGLISH II ADVANCED PLACEMENT PSYCHOLOGY
12345 JANE ANYPERSON SPANISH I         MEDIA CENTER ASSISTANT

How do you relate CurrentCourse "HONORS ENGLISH II" from to CourseSelection
"ADVANCED PLACEMENT PSYCHOLOGY" ?
"SPANISH I" to "MEDIA CENTER ASSISTANT" ?
AND NOT "HONORS ENGLISH II" to any others ?

j_mact
Starting Member

17 Posts

Posted - 2007-04-26 : 12:19:26

Well, that's where I am getting confused. I don't want to relate them at all. The only thing the tables have in common is the studentid, firstname and lastname. I am doing the join on studentid, and I don't understand why the course name in table a keeps repeating.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources