Create table using joins

[mina10a]

Please help me. iTs very urgent.

I have three Tables
1 Student_master:
stu_id(pk),name ,phoneno,father name,address,co llegename,class ,photo;

2 Student_detail
slno(pk),stu_id (fk), subject,teacher name,stu_name,b atchday,batchti me

3 fee_detail
stu_id(fk),Tota l amount, Paid amount;

my problem is that i have to retrieve the following fields where teachername is given.

stu_id,stu_name ,classname,subj ect,paidMoney where teachername=som evalue

i tried to write the query but i failed.
plz anyone can help me .
i advancely thanks .

[iburyak]

Try this:

[PHP]
Select sd.stu_id, sm.name as stu_name, sm.class as classname, sd.subject, [Paid amount] as paidMoney
From Student_detail sd
Join Student_master sm on sd.stu_id = sm.stu_id
Join fee_detail fd on sd.stu_id = fd.stu_id
Where teachername = somevalue[/PHP]

Good Luck

[mina10a]

hi thanks for help
i trid this query. but it gives the error as

Syntax error near from Clause

I give the query which i write:

select sd.stu_id,sm.St u_name as studentName,sm. className as cname,sd.subjec t as ssubject,fd.Pai d_Money as paidmoney from xiStu_detail sd join xiStudent_maste r sm on sd.stu_id=sm.st u_id join fee_Detail fd on sd.stu_id=fd.st u_id where Teachername='KK K'

where is the error i could not find.
if u give me solution i grateful to u.

[iburyak]

Everything looks good.

Try this:

[PHP]
Select *
from xiStu_detail sd
join xiStudent_maste r sm on sd.stu_id = sm.stu_id
join fee_Detail fd on sd.stu_id = fd.stu_id
where Teachername = 'KKK'[/PHP]

Let me know if you have an error this way.

[iburyak]

Check this table's name

from xiStu_detail sd

Maybe it should be

from xiStudent_detai l sd

[mina10a]

thanks .
its work.
now i want to write an trigger. so i dont know how to write trigger.
if any records from my student_master table is changed or deleted a trigger is fired.
all the old data wihch is updated is transfer to the new table
and new data is update in the student master able.
when record is delete the deleted record is enter to the new table and delete from the student_master table.

sorry for my language.plz help me
and thanks

[iburyak]

It should look something like this:

[PHP]

Create trigger tIU_TableName ON TableName FOR DELETE,UPDATE
AS
BEGIN
Insert into NewTable
Select * from deleted
END

[/PHP]

Don't forget to change table names.

Good Luck

[mina10a]

thanks its working.
thank u for help me

[mina10a]

hi i write an update triggre
it works fine . but it adds two record of the updated record in new table
my trigger code is as follow:

CREATE TRIGGER trig_update_adv Master ON [dbo].[Adv_Master]
for update
as
insert into trig_delete_adv Master(deleteti me,operation,ad v_code,memship_ no,memship_dt,e nrollmt_no,enro llmt_dt,auth_en rolled,oth_bar_ association,led ger_id,id_card_ no,library_mems hip_no,adv_name ,dob,gender,off _add,res_add,ch _add,off_phone, res_phone,ch_ph one,fax,mobile, email,type_of_m emship,father_h usband_name,des ignation,status ,extra_info) select getdate() as dt,'Update' as op,* from deleted

where is the error i m unable to find. plz help me. what is wrong with this code

[iburyak]

Check if you have duplicates in a table. It is most possilbe cause of a problem.
When you do update how many records is actually updated?

Also you can do this but it is not a cause of a problem and I wouldn't recomend it:

[PHP]
select distinct getdate() as dt,'Update' as op,* from deleted [/PHP]

Good Luck.

[mina10a]

Check if you have duplicates in a table. It is most possilbe cause of a problem.
When you do update how many records is actually updated?

Also you can do this but it is not a cause of a problem and I wouldn't recomend it:

[PHP]
select distinct getdate() as dt,'Update' as op,* from deleted [/PHP]

Good Luck.

thanks .its working.
thank u very much.