Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

I have one master table (teacher) structured like

teacherId   Name        Class
1       Praveen     10
2       John        9

and having a child table (student) structured like

studentId   teacherId   Name
1       1       David
2       1       Creg
3       2       Mike
4       2       Steve
5       2       Jim

How I can get a result set like

teacherId   teacher     studentId_1 student_1   studentId_2 student_2   studentId_3 student_3

1       Praveen     1       David       2       Creg        null        null
2       John        3       Mike        4       Steve       5       Jim

Thanks in advance..

Regards, Praveen

share|improve this question
2  
This is not something along the lines of how SQL is to be used! What would happen, if a teacher had 65536 students for example? You should use a normal query, with a single join, then from your application, aggregate the students under the teachers. The query: SELECT t.teacherId, t.Name, s.studentId, s.Name FROM teacher t JOIN student s ON t.teacherId=student.teacherId – ppeterka Oct 17 '12 at 7:56

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.