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.
| Author |
Topic |
|
sqlxls
Starting Member
4 Posts |
 Posted - 2005-11-19 : 08:17:36
|
| Thanks in Advance!!I have three tables as explained below.TEACHERS ---------teacher_id tname stateSUBJECTS --------subjectsname TEACHER_SUBJECTS---------------teacher_idsubject_id All I need is a query that will return me the list of all teachers in a particular State and who teach a particular Subject (teachers can teach multiple subjects)The query needs to return the data as follows:Name --- State --- Subjects (ignore '---')-------------------------------John --- NY --- (Math,Science)Ramesh --- NY --- (CS, Math)Kelly --- NY --- (Chemistry)Wang --- NY --- (CS, Chemistry, Biology)I would like to obtain the above info in a single query with all the matching records if possible. I am assuming the Subjects has to be some sort of nested query/function? Not sure how to go about doing so. THANKS!! |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-11-19 : 08:27:10
|
| Select T.tname, T.state, S.sname from TEACHERS T inner join TEACHER_SUBJECTS TS on T.teacher_id =TS.teacher_id inner join Subjects S on TS.subject_id=S.subjectMadhivananFailing to plan is Planning to fail |
 |
|
|
sqlxls
Starting Member
4 Posts |
Posted - 2005-11-19 : 08:32:36
|
| you are right, it needs subject_id.But the query you gave me will return records like so right?John , NY, MathJohn , NY, ScienceRamesh , NY, CSRamesh , NY, MathHow do I get?John , NY, (Math, Science)Ramesh , NY, (CS, Math) |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
sqlxls
Starting Member
4 Posts |
Posted - 2005-11-19 : 08:42:29
|
| It will be shown on an ASP page, I was hoping not to do so in the ASP pages, cos I will also be parsing results if that is the right term also in teh stored procs, 'showing reults 1- 10 of 90 etc. etc...any links for that? I had one earlier, will ahve to search for it again, my SQL is rusty..DO you think it is more efficient to do so in the front end rather than in the db? |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
sqlxls
Starting Member
4 Posts |
Posted - 2005-11-19 : 09:17:08
|
| yep. that's it, actually found the article on this site under stored procs although it uses temp tables. |
|
|
|
|
|
|
|
|
Sql Help needed. Search and Display.