Create table using joins

[mina10a]

Please help me. iTs very urgent.

I have three Tables
1 Student_master:
stu_id(pk),name,phoneno,fathername,address,college name,class,photo;

2 Student_detail
slno(pk),stu_id(fk), subject,teachername,stu_name,batchday,batchtime

3 fee_detail
stu_id(fk),Total amount, Paid amount;

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

stu_id,stu_name,classname,subject,paidMoney where teachername=somevalue

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.Stu_name as studentName,sm.className as cname,sd.subject as ssubject,fd.Paid_Money as paidmoney from xiStu_detail sd join xiStudent_master sm on sd.stu_id=sm.stu_id join fee_Detail fd on sd.stu_id=fd.stu_id where Teachername='KKK'

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_master 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_detail 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_advMaster ON [dbo].[Adv_Master]
for update
as
insert into trig_delete_advMaster(deletetime,operation,adv_cod e,memship_no,memship_dt,enrollmt_no,enrollmt_dt,au th_enrolled,oth_bar_association,ledger_id,id_card_ no,library_memship_no,adv_name,dob,gender,off_add, res_add,ch_add,off_phone,res_phone,ch_phone,fax,mo bile,email,type_of_memship,father_husband_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.