471,086 Members | 936 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,086 software developers and data experts.

Create table using joins

17
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 .
May 1 '07 #1
10 1879
iburyak
1,017 Expert 512MB
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
May 1 '07 #2
mina10a
17
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.
May 2 '07 #3
iburyak
1,017 Expert 512MB
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.
May 2 '07 #4
iburyak
1,017 Expert 512MB
Check this table's name

from xiStu_detail sd

Maybe it should be

from xiStudent_detail sd
May 2 '07 #5
mina10a
17
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
May 3 '07 #6
iburyak
1,017 Expert 512MB
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
May 3 '07 #7
mina10a
17
thanks its working.
thank u for help me
May 4 '07 #8
mina10a
17
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
May 4 '07 #9
iburyak
1,017 Expert 512MB
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.
May 4 '07 #10
mina10a
17
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.
May 5 '07 #11

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Ralph Freshour | last post: by
2 posts views Thread by terence.parker | last post: by
7 posts views Thread by Jake | last post: by
4 posts views Thread by staeri | last post: by
20 posts views Thread by p175 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.