A Query in which the quantity of deduced columns isn't known.

[vedro-compota]

Hello )) Please tell me - How to receive surnames of students and their mark in all subjects?
in this form -

SURNAME1 1hismark1 1hismark1 1hismark1 1hismark1.....
SURNAME2 2hismark1 2hismark1 2hismark1 2hismark1.....

I use this database -

table STUDENT
(
  STUDENT_ID NUMERIC not null,
  SURNAME    VARCHAR(60),
);
table SUBJECT
(
  SUBJ_ID   NUMERIC not null,
  SUBJ_NAME VARCHAR(100),
);
 table EXAM_MARKS
(
  EXAM_ID    NUMERIC not null,
  STUDENT_ID NUMERIC not null,
  SUBJ_ID    NUMERIC not null,
  MARK       NUMERIC
);

Thank you in advance ))

[drjohn]

Easiest way is to do a normal join, ordering by student_id, then process the output in code - PHP or whatever you are using.

However, unless everyone is taking the very same subjects, and you include subject name and sort on that too, there will be no relationship between any given pair of students and their hismark1, hismark2, etc.

So if all you wanted was a set of marks, join student to the marks table; but if you have everyone taking the same subjects and want some meaning to the vertical columns of hismark1, hismark2, you'd have to join student to mark and then mark to subject, and sort by student_id, then subject, then post process the output.

PS Merry Xmas, I like people to visit my sites.

[vedro-compota]

It is clear nothing)) But I have visited your sites))

PHP or whatever you are using.

I need do it only by SQL :))

[smantscheff]

select surname, subj_name, mark
from student st
left join exam_marks em on st.stud_id=em.stud_id
join subject su on em.subj_id=em.subj_id

If this does not fit your needs, please post a complete test case with table structure, test data, your query, the expected results and the actual results. Then it will become clearer to you and to us what you are aiming at.

[vedro-compota]

Thanks for the replies friends))
smantscheff - You suggested the answer in a form like this -

John - Mathematics - 86

But I need all John's marks of John located in the same row.
Like this -

John |mathematics - 86 | Biology - 53 | Computer Science (Certainly ;)) - [B]99[/B]

is it possible to make such by the SQL?

[smantscheff]

Not in standard SQL. But have a look at the MySQL function GROUP_CONCAT() which will help you.
And submit a complete test case if you want more help.

[vedro-compota]

thank you very much ,smantscheff ))