How to create multiple return subquery?

Asked Oct 20 '14 at 18:15

Active Oct 20 '14 at 18:52

Viewed 18 times

I have two databases, one containing students and the other one containing their marks. I want to be able to extract from them via single query list of students' names from first table and their maximal mark+name of subject they got it for. I know this example does not work:

SELECT LastName, FirstName, ID, 
(
    SELECT TOP 1 MIN(Mark), Subject
    FROM Marks as o
    GROUP BY Mark
 ) as (minMark, minSubject),
(
    SELECT TOP 1 MAX(Mark), Subject
    FROM Marks as O
    WHERE  o.StudentID = C.ID
) as (maxMark, maxSubject)
FROM Students as C

But it shows perfectly what I need. How can one achieve an effect like this?

+It is not duplicate, mysql solution will do me nothing good.

edited Oct 20 '14 at 18:52

asked Oct 20 '14 at 18:15

Ginterhauser

if this is mysql then TOP doesnt work.. a correlated subquery can only have one column as well.. you can't do a group by.. you should probably do some research – John Ruddell Oct 20 '14 at 18:17
Actually this is mssql, and i can do both TOP and GROUP BY – Ginterhauser Oct 20 '14 at 18:38
then why is it tagged mysql? and there arent any other tags? – John Ruddell Oct 20 '14 at 18:40
Sorry, my bad. I overlooked that – Ginterhauser Oct 20 '14 at 18:52
its ok... I was just like umm this is completely wrong syntax! lol – John Ruddell Oct 20 '14 at 18:53
Well, everything's fine, but i still need that query xD – Ginterhauser Oct 20 '14 at 19:05

How to create multiple return subquery?

0 Answers0