sql inner join need help - 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

General SQL Server Forums

New to SQL Server Programming

sql inner join need help

Author

Topic

prettyjenny
Yak Posting Veteran

57 Posts

Posted - 2010-12-27 : 14:05:33

Hello, I am trying to do the student evaluation and stuck in this problem.
Each student evaluates himself and all his friends in the same class. I have a sample table below. On the query, I tried to join the table to itself to get a new column calls "StudentID-Change" to list a student himself and his class mates. But I can't get the right result.
SELECT Table1.StudentID, Table1_1.StudentID, Table1.DepartmentID
FROM Table1 LEFT JOIN Table1 AS Table1_1 ON Table1.StudentID = Table1_1.StudentID;

ID StudentID ClassID
1 22 100
2 33 100
3 44 100

Here is the result I want to see in query.
StudentID StudentID-Change ClassID
22 22 100
22 33 100
22 44 100
33 22 100
33 33 100
33 44 100
44 22 100
44 33 100
44 44 100

Can anyone please help? What did I do wrong? Why can’t I get the right result using the that join?
Thanks very much.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-12-27 : 14:26:20

For SQL 2005 and higher you can use cross apply
Jim

select t2.studentid,t1.studentid,t1.ClassId
from table1 t1

cross apply (select * from table1) t2

Everyday I learn something that somebody else already knew

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-12-27 : 14:28:10

or a cross join
select t2.studentid,t1.studentid,t1.ClassId
from table1 t1

cross join table1 t2

Jim

Everyday I learn something that somebody else already knew

prettyjenny
Yak Posting Veteran

57 Posts

Posted - 2010-12-28 : 10:08:45

It worked perfect. I have one more question:

Currently, I have a view called "StudentScore" with data as:

StudentID StudentID-Change ClassID
22 22 100
22 33 100
22 44 100
33 22 100
33 33 100
33 44 100
44 22 100
44 33 100
44 44 100

And, I have a table called "tblScore" that students evaluate themselves and their class mates as:

StudentID StudentID-Change ClassID Score

22 22 100 90
22 33 100 85
33 22 100 78
44 33 100 96

I like to join StudentScore with tblScore to get score in like this:

StudentID StudentID-Change ClassID
22 22 100 90
22 33 100 85
22 44 100
33 22 100 78
33 33 100
33 44 100
44 22 100
44 33 100 96
44 44 100

It is null if any row does not have score yet, so I created a query like:

select StudentScore.*, tblScore.*

from StudentScore, tblScore

where StudentScore.ClassID=tblScore.ClassID

But it returns all duplicates.

How do I fix the query to return the correct result as above?

Thanks.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-12-28 : 10:24:00

I think you need to join on more than classID, and definitely a LEFT Join to get all the records in studentScore that may not have a match in tblScore

Jim



SELECT t1.*,t2.Score
FROM       @studentScore t1
LEFT JOIN  @tblScore     t2
ON
    t1.studentID = t2.studentID
and t1.StudentIdChange = t2.StudentIdChange
and t1.classId         = t2.classID

Everyday I learn something that somebody else already knew

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-28 : 10:26:52

select ss.*, ts.score
from StudentScore ss
left join tblScore ts
on ss.StudentID = ts.StudentID
and ss.[StudentID-Change] = ts.[StudentID-Change]
and ss.ClassID = ts.ClassID

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

prettyjenny
Yak Posting Veteran

57 Posts

Posted - 2010-12-28 : 10:56:01

Perfect!

Thank you very much!

There is no stupid question.
Asian Girls at Free Online Dating

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-28 : 11:06:39

quote:
Originally posted by prettyjenny

Perfect!

Thank you very much!

There is no stupid question.

Did you put those links in and are they really what they appear to be?
I would edit them out before you get locked out.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources