CODEWITHSUNDEEP
mysql
willy andika
willy andika

Reputation: 83

query list student where grade just b

i have some table

table: mhsw
nim; name; prodi
1001; willy; SIF
1002; karel; SIF
1003; fikri; TIF
1004; wawan; DPI

table: krs
mkid; mkname; grade; final
1001; mk1; A; Y
1001; mk2; B; Y
1001; mk3; B; Y
1002; mk2; C; Y
1002; mk3; B; Y
1002; mk1; D; Y
1003; mk1; A; Y
1003; mk2; B; Y
1003; mk3; A; Y
1004; mk1; A; Y
1004; mk2; D; Y
1005; mk3; A; Y

i want my result
nim; name; prodi
1001; willy; SIF
1003; fikri; TIF

the condition is print mhsw list where all mkid on krs table is all grade A,B,C and final is Y and not to display when grade is D or E

i need the mysql query thanks

Upvotes: 0

Views: 43

Answers (2)

Blair
Blair

Reputation: 35

SELECT mhsw.*,tbl.*
FROM mhsw 
INNER JOIN 
     (SELECT mkid
     FROM krs
     Where grade IN ('A', 'B', 'C') 
     AND final = 'Y') tbl ON mhsw.nim = tbl.mkid

If mhsw.nim = tbl.mkid has multiple row, it would display as:

nim; name; prodi; mkid; mkname; grade; final;
1001; willy; SIF; 1001;mk1; A; Y;
1001; willy; SIF; 1001;mk2; B; Y;
1001; willy; SIF; 1001;mk3; B; Y;
.
..
....
1004; wawan; DPI; 1004; mk1; A; Y;

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520898

One optional is to use conditional aggregation on the krs table to identify which students had only A, B, or C grades along with all Y for the final.

SELECT t1.nim,
       t1.name,
       t1.prodi
FROM mhsw t1
INNER JOIN
(
    SELECT mkid
    FROM krs
    GROUP BY mkid
    HAVING SUM(CASE WHEN grade NOT IN ('A', 'B', 'C') THEN 1 END) = 0 AND   -- only A,B,C
           SUM(CASE WHEN final = 'Y' THEN 1 END) = COUNT(*)   -- all final is Y
) t2
    ON t1.nim = t2.mkid

Upvotes: 0

Related Questions