Sql Server Query

Question

0.00/5 (No votes)

See more:

SQL-server-2000

Hi all,

I am planning to execute a query with 4 table student, Markdetails,subject and department. I used the query

SQL

SELECT S.regno,S.studentname,S.gender,CM.classname,SM.subjectname,MS.mark FROM STUDENT S, MARKDETAILS MS,CLASSMASTER CM, SUBJECTNAME SM WHERE S.classid=CM.classid AND MS.subjectid=SM.subjectid AND  S.studentid=MS.studentid

And i got the output as

regno studentname gender classname subjectname mark
1 djsdsj 1 CSE TAMIL 50
1 djsdsj 1 CSE ENGLISH 51
1 djsdsj 1 CSE MATHS 53
1 djsdsj 1 CSE SCIENCE 54
1 djsdsj 1 CSE SOCIALSCIENCE 65
2 sdf 2 ECE TAMIL 61
2 sdf 2 ECE ENGLISH 62
2 sdf 2 ECE MATHS 63
2 sdf 2 ECE SCIENCE 64
2 sdf 2 ECE SOCIALSCIENCE 65

:confused: But my expected output is

regno studentname gender classname subjectname mark
1 djsdsj 1 CSE TAMIL 50
ENGLISH 51
MATHS 53
SCIENCE 54
SOCIALSCIENCE 65
2 sdf 2 ECE TAMIL 61
ENGLISH 62
MATHS 63
SCIENCE 64
SOCIALSCIENCE 65

Kindly help me in this.

Thanks in Advance.

Regards,
S.J.Parrthipan

Posted 27-Sep-10 7:40am

S.J.PARRTHIPAN

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

E.F. Nijboer · Answer 1 · 2010-09-27T08:30:00

Solution 1

The select command states SELECT S.regno,S.studentname,S.gender,CM.classname,SM.subjectname,MS.mark so these fields are returned for each row.

Read up on relational algebra and you'll understand why this is.

http://en.wikipedia.org/wiki/Relational_[^][^]

Good luck!

Posted 27-Sep-10 8:30am

E.F. Nijboer

Updated 27-Sep-10 10:19am

v2

Comments

Henry Minute 27-Sep-10 14:51pm

You are of course correct but if the OP is unable to read the SQL Server documentation, I doubt this will actually help him. :)

AspDotNetDev 27-Sep-10 16:07pm

I think you meant to link here: http://en.wikipedia.org/wiki/Relational_algebra

E.F. Nijboer 27-Sep-10 16:22pm

Correct. I usually check pasted links because for some reason because this has a bug unfortunately. Must have forgotten it this time.

1Numpty · Answer 2 · 2010-09-27T09:56:00

I should try a join somewhere like this :

SELECT top 100 S.regno, S.studentname, S.gender, CM.classname, SM.subjectname, MS.mark
FROM STUDENT S, SM.SUBJECTNAME

inner left join CLASSMASTER CM
on CM.classid=S.classid
inner left join MARKDETAILS MS
on MS.subjectid=SM.subjectid
AND S.studentid=MS.studentid

This probably don't work exactly but you can tweek it to get the desired effect.

Good luck

Hiren solanki · Answer 3 · 2010-09-27T19:52:00

Solution 3

The output you are trying to accomplish is probably not supported by sql,

Because the rows returned from sql Query will have the same columns,
you will need some code side efforts to get a expected result.

Please vote and Accept Answer if it Helped.

Posted 27-Sep-10 19:52pm

Hiren solanki