SQL query to filter combination of 3 tables

user_16579445 0
Viewed 63

I have 3 tables

Student

1 ABC
2 XYZ
3 PQR

Subject

10 Maths
11 Science
12 English

Marks

SID SBID    MARKS
1   10  60
1   11  80
1   12  90
2   10  50
2   11  40
2   12  30
3   10  45
3   11  49
3   12  25

I need to get a result with the columns considering 50 and above is Pass mark.

StudentName, Maths_Marks, Science_Marks, English_Marks, Total, Status(P/F)

ie.

ABC   60  80  90  250  P
XYZ   50  40  30  120  F
PQR   45  49  25  119  F
sql
2 Answers

This is what you are looking for :

DECLARE @Student TABLE(
            ID  int,
            Name  VARCHAR(100)
    )
    
    INSERT INTO @Student SELECT '1','ABC'
    INSERT INTO @Student SELECT '2','XYZ'
    INSERT INTO @Student SELECT '3','PQR'



    DECLARE @Subject TABLE(
            ID  int,
            Name  VARCHAR(100)
    )
    
    INSERT INTO @Subject SELECT '10','Maths'
    INSERT INTO @Subject SELECT '11','Science'
    INSERT INTO @Subject SELECT '12','English'


        DECLARE @Marks TABLE(
            SID   int,
            SBID    int,
            MARKS   int
    )
    
    INSERT INTO @Marks SELECT '1','10','60'
    INSERT INTO @Marks SELECT '1','11','80'
    INSERT INTO @Marks SELECT '1','12','90'
    INSERT INTO @Marks SELECT '2','10','50'
    INSERT INTO @Marks SELECT '2','11','40'
    INSERT INTO @Marks SELECT '2','12','30'
    INSERT INTO @Marks SELECT '3','10','45'
    INSERT INTO @Marks SELECT '3','11','49'
    INSERT INTO @Marks SELECT '3','12','25'



        DECLARE @FinalResult TABLE(
            Name  VARCHAR(100),
            Maths_Marks int,
            Science_Marks int,
            English_Marks int
            )
    

    INSERT INTO @FinalResult
        select 
        S.name,
        M.MARKS as Maths_Marks,
        (
    select M2.MARKS as 'Science_Marks' from @Student S2
    join @Marks M2 on M2.SID = S2.ID
    join @Subject SB2 on SB2.ID = M2.SBID
    where SB2.Name ='Science'
    and S2.ID = S.ID
        ) as Science_Marks,
        (
    select M2.MARKS as 'English_Marks' from @Student S2
    join @Marks M2 on M2.SID = S2.ID
    join @Subject SB2 on SB2.ID = M2.SBID
    where SB2.Name ='English'
    and S2.ID = S.ID
        ) as English_Marks 
    
        from @Student S 
    join @Marks M on M.SID = S.ID
    join @Subject SB on SB.ID = M.SBID
    where SB.Name ='Maths'
    


    SELECT  *,fn2.Maths_Marks + fn2.English_Marks +fn2.Science_Marks as Total  ,
    CASE WHEN fn2.Maths_Marks > 50 THEN 'P'
    ELSE 'F'
END AS Status
    from @FinalResult fn2

And finally the Result looks like : enter image description here

user_76525160

you can use pivot. Of course, you did not tag the name of the database and I got the output in sql server.

select StudentName,
       Maths as Maths_Marks,
       Science as Science_Marks,
       English as English_Marks,
      (Maths+Science+English) as Total,
      case when Maths > 50 then 'P' else 'F' end as Status
from
(
   select s.Name as StudentName,m.Marks,sb.Name as sbname
   from Marks m join Subject sb on m.SBID = sb.ID join Student s on m.SID = s.ID
) as SourceTable
pivot (
    max (Marks)
    for sbname
    in (Maths,Science,English)
) as piv

demo in db<>fiddle

user_150195360
Related