multiple query in one query where

Question

I wrote this query for finding the highest score from 2 tables (Selected Item: StudentId and Score), and now I want to select some other data: (StudentName, StudentImage, ...) from other tables that must filter items using StudentId

my query returns:

StudentId       HighScoreUser
-1              250
-2              100
-3               90
-4               80
-5               40

For showing data in a grid, I need the Student Name, ... so I must use StudentId to find the info for the specific user:

CREATE PROCEDURE SelectTopYear  
AS
    SELECT TOP 5 StudentId, ISNULL(SUM(Score),0) As HighScoreUser
FROM (SELECT StudentId, Score FROM tbl_ActPoint
      UNION ALL
      SELECT StudentId, Score FROM tbl_EvaPoint
     ) as T 
GROUP BY  StudentId ORDER BY HighScoreUser DESC
RETURN 0

now I want to select some other data: (StudentName, StudentImage, ... => if these values are same for individual student_id, then use these in group by as well. Else decide which column value you need for each student id and perform aggregate function of these columns. In that case give some sample data with all the required column and your expected output — Utsav
– Utsav, Commented Jun 6, 2017 at 16:20

Gordon Linoff · Accepted Answer · 2017-06-06 16:30:09Z

1

You can use a CTE (or subquery) and JOIN:

WITH s as (
      SELECT TOP 5 StudentId, ISNULL(SUM(Score),0) As HighScoreUser
      FROM (SELECT StudentId, Score FROM tbl_ActPoint
            UNION ALL
            SELECT StudentId, Score FROM tbl_EvaPoint
           ) s
      GROUP BY  StudentId
      ORDER BY HighScoreUser DESC
     )
SELECT . . .
FROM s JOIN
     othertable ot
     ON s.StudentId = ot.StudentId;

Fill in the appropriate column names and table names.

answered Jun 6, 2017 at 16:30

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

multiple query in one query where

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related