Plz help me... - 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

Plz help me...

Author

Topic

soori457
Yak Posting Veteran

85 Posts

Posted - 2009-11-23 : 05:19:03

Hai All,

student (table)

student_id first_name last_name
101 suresh kumar
102 ramesh kumar
103 ganesh kumar
104 kamesh kumar
105 satish kumar

question (table)

question_id question student_id date_created
1 abc 101 2009-03-25
2 xyz 103 2009-03-15
3 pqr 104 2009-06-23

answer (table)

answer_id question_id answer student_id date_created
1 1 asdg 102 2009-06-23
2 1 dfdf 103 2009-06-24
3 2 sfef 101 2009-06-25
4 3 segseg 103 2009-06-26
5 2 wff 104 2009-06-27

In the following tables
I need to retrieve records with question, posted_by, answered date, answered by columns.

I have written the following query

select q.question, q.student_id as posted_by, q.date_created, a.student_id as answered_by from question q
inner join answer a on a.question_id = q.question_id

But I want to display the student names instead of student_id in posted_by and answered_by columns

Please help me in writing subquery

Thanks in Advance

Suresh Kumar

kbhere
Yak Posting Veteran

58 Posts

Posted - 2009-11-23 : 06:04:22

Since, you want to display columns from all the tables.. Definitely, you have to use one more join.. In this case subquery will not provide you the solution as required by you..

Balaji.K

soori457
Yak Posting Veteran

85 Posts

Posted - 2009-11-23 : 06:26:58

Ya, I tried like this
but am getting error

select q.question, q.student_id as posted_by,
(select s.first_name + ' ' + s.last_name from student where s.student_id = q.student_id) as posted_by_student,
q.date_created,
a.student_id as answered_by,
(select s.first_name + ' ' + s.last_name from student where s.student_id = a.student_id) as answered_by_student
from question q
inner join answer a on a.question_id = q.question_id
inner join student s on s.student_id = q.student_id

Suresh Kumar

peterkirubakaran
Starting Member

12 Posts

Posted - 2009-11-23 : 07:29:29

hi...
here you will get the name for posted_by and answered_by..
add other columns which you want to display...

select s.first_name as posted_by , ss.first_name as answered_by
from question q inner join answer a
on a.question_id=q.question_id inner join student s
on s.student_id=q.student_id inner join student ss
on ss.stdid=a.stdid

Peets

soori457
Yak Posting Veteran

85 Posts

Posted - 2009-11-23 : 08:44:41

Thanks Peter

Suresh Kumar

peterkirubakaran
Starting Member

12 Posts

Posted - 2009-11-23 : 09:43:55