Resolve this SQL Query

Database

Resolve this SQL Query

Hi All,

I have

table a having columns student_no(PK) and name.
data is 123 and xxxxxx.

table b having columns student_no(FK) category and marks
data is 123 maths 55
123 science 60
123 english 80.

Now i want the student details as

name maths science english
xxxxxx 55 60 80

but my sql is giving output as

xxxxxx 55 ? ?
xxxxxx ? 60 ?
xxxxxx ? ? 80

Please send the query to me, its urgent to me to solve my work.
2 REPLIES
Enthusiast

Re: Resolve this SQL Query

select
a.empno,
a.empname,
max(a.maths) maths,
max(a.english) english,
max(a.science) science
from
(select
s.empno,
s.empname,
(case when m.subj='maths'
then
m.mark
else
null
end) Maths,
(case when m.subj='english'
then
m.mark
else
null
end) English,
(case when m.subj='science'
then
m.mark
else
null
end) Science
from stu s, stumark m
where s.empno = m.empno) a
group by a.empno,a.empname;
Enthusiast

Re: Resolve this SQL Query

sample output:

empno empname maths english science
100 sarathy 40 80 60