want 1 table records in another table column field

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.

All Forums

Other Forums

MS Access

want 1 table records in another table column field

Author

Topic

alkeshrp
Starting Member

1 Post

Posted - 2011-11-21 : 05:54:28

I have 4 table

Table : student

STID StName Phone Add1 Add2
1 Frank 44444 NewYork USA
2 Jack 33333 London UK
3 Nia 22222 Paris France
4 James 45343 Sydney Australia
5 Sam 11111 Auckland NewZealand

Table : Subject
SdName TeacherName TotalMarks
Science Ben 50
Computer Mark 30
English Larry 50
Physics Hilton 30

Table : StudentRegister

TrId Stid Date Time
1 2 10-03-2011 03:00pm
2 1 11-03-2011 03.00pm
3 2 12-03-2011 12.00pm
4 3 15-03-2011 9:00 am
5 2 17-03-2011 12:00 pm

Table : StudentsMark

Trid StId SdName Marks
1 2 Science 30
1 2 Computer 15
2 1 Science 35
2 1 English 20
2 1 Physics 25
3 2 Science 35
3 2 Computer 25
4 3 Computer 30
4 3 Science 25
5 2 Physics 25

I want query that shows this type of table

Stid StName Science Computer English Physics Total Per
1 Frank 35 20 25 80 50.00%
2 Jack 35 25 25 85 53.16%
(max out of 2 try )
3 Nia 25 30 55 34.38%
And so on…

I have solution to input like this and can do output like this in visual basic with lots of coding but I want 1 query to get this…..
this is simple illustration of my complex database bcoz my subject can change, u can add more subject so I cant use simple 1 table for all.
if any one have the solution plz help me…
Thanks in advance…..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-21 : 06:28:14

[code]
SELECT s.STID,
s.StName ,
[Science], [Computer], [English], [Physics],
[Science]+ [Computer]+ [English]+ [Physics] AS Total,
([Science]+ [Computer]+ [English]+ [Physics])*100.0/(SELECT SUM(TotalMarks) FROM Subject) AS Per
FROM student s
inner join StudentsMark sm
ON sm.StId =s.StId
PIVOT(SUM(Marks) FOR SdName IN ([Science], [Computer], [English], [Physics] ]))p
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources