Help On appending query in sql

Posted by Vijayar under ASP.NET on 1/12/2012 | Points: 10 | Views : 834 | Status : [Member] | Replies : 1
I need to add where condition to the following query.

select tc.TeacherassigntoClass_Id,cs.ClassSubject_Name,tc.Description,c.Class_Name,t.Teacher_Name ,tc.Status,l.location name
from TeacherassigntoClass tc,ClassSubject cs,dbo.Class c,dbo.Teacher t where tc.Class_Id=c.Class_Id and tc.ClassSubject_Id=cs.ClassSubject_Id and tc.Teacher_Id=t.Teacher_Id order by TeacherassigntoClass_Id desc.I need to append where query based on location name .



Posted by: Ogipansrk on: 1/12/2012 [Member] Starter | Points: 25


Better use joins , below is a similar example:

create table class
classid int,
classname nvarchar(50)

insert into class values(1,'class1')
insert into class values(2,'class2')
insert into class values(3,'class3')

create table subjects
subjectid int,
subjectname nvarchar(50)

insert into subjects values(1,'Computers')
insert into subjects values(2,'Biology')
insert into subjects values(3,'English')

create table location
locationid int,
locationname nvarchar(50)
insert into location values(1,'hyderabad')
insert into location values(2,'hytech city')

create table teacher
teacherid int,
teachername nvarchar(50),
classid int,
subjectid int,
locationid int

insert into teacher values(1,'Ogipansrk',1,1,1)
insert into teacher values(2,'Billgates',2,2,2)

select t.teacherid,t.teachername,c.classname,s.subjectname
from teacher t
inner join class c on t.classid=c.classid
inner join subjects s on t.subjectid=s.subjectid
where t.locationid =1


Vijayar, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response