why don't group by work????

[farid25]

there are three tables i want to query i use the following statement

SELECT dbo.t_student_course.StudentId,dbo.t_books.book_na me
FROM dbo.t_student_course , dbo.t_students , dbo.t_books, dbo.t_groups
WHERE dbo.t_books.book_id = dbo.t_student_course.book_id
AND dbo.t_books.book_id = dbo.t_student_course.book_id AND dbo.t_student_course.StudentId = dbo.t_students.Student_ID
Group by dbo.t_student_course.StudentId, dbo.t_books.book_name

i got the following result

Student_id book_name
========= ========
4 City
5 City
3 Detective
5 Detective
1 PC
3 PC


when i use the following statement

SELECT dbo.t_student_course.StudentId
FROM dbo.t_student_course , dbo.t_students , dbo.t_books, dbo.t_groups
WHERE dbo.t_books.book_id = dbo.t_student_course.book_id
AND dbo.t_books.book_id = dbo.t_student_course.book_id AND dbo.t_student_course.StudentId = dbo.t_students.Student_ID
Group by dbo.t_student_course.StudentId


i got the following result


Student_id
=========
1
3
4
5

i want to get the following reult

Student_id book_name
========= ========
4 City
5 City
3 Detective
1 PC


what i should do to get the prevoius result. why don't group by work????

thanks for your help

[Banfa]

I do not think the group by clauses in either statement as you are not using an summary functions (like COUNT, SUM, AVG etc).

In the second statement you removed dbo.t_books.book_name from the SELECT statement, try

SELECT dbo.t_student_course.StudentId, dbo.t_books.book_name
FROM dbo.t_student_course , dbo.t_students , dbo.t_books, dbo.t_groups
WHERE dbo.t_books.book_id = dbo.t_student_course.book_id
AND dbo.t_books.book_id = dbo.t_student_course.book_id AND dbo.t_student_course.StudentId = dbo.t_students.Student_ID