CODEWITHSUNDEEP
sqlsql-servert-sql
user7647348
user7647348

Reputation:

multiple query in one query where

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

Upvotes: 0

Views: 66

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

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.

Upvotes: 1

Related Questions