Hello Eva,

I'm sure Mike and others can think of a better way, but one way is to use the UNION operator to join result sets:

select name, subject, marks
from student

UNION

select name,1,.null. from student where name not in
(select name from student where sub_id = 1)

UNION

select name,2,.null. from student where name not in
(select name from student where sub_id = 2)

UNION

select name,3,.null. from student where name not in
(select name from student where sub_id = 3)


>Hi all...
>
>Here's 2 tables:
>
>Table : Student
>(Name) (Sub_id) (Marks)
>Amy 1 30
>Amy 2 50
>Peter 2 40
>Peter 3 40
>Peter 4 50
>
>Table : Subject
>(Sub_id) (Desp)
>1 Chinese
>2 English
>3 Maths
>4 Phy
>5 Chem
>
>
>How to get the result on each student that
>1) Chin, Eng, Math must display no matter he/she takes that subject or not
>2) Do not display Phy/Chem if he/she not takes
>
>Results:
>(Amy)
>Chinese 30
>Eng 50
>Maths null
>
>(Peter)
>Chinese null
>Eng 40
>Maths 40
>Phy 50
>
>Much Thanks!
>Eva ^.^