CODEWITHSUNDEEP
mysql
AdityaDees
AdityaDees

Reputation: 1047

mysql sub query for find grade

I have tabel like this

+----+-------+------------+-------+
| No | NIK   | Nama       | Nilai |
+----+-------+------------+-------+
|  1 | 06001 | Syafruddin |    70 |
|  2 | 20000 | Maman      |    90 |
|  3 | 30000 | Linda      |    40 |
|  4 | 40000 | Tesa       |    71 |
+----+-------+------------+-------+
+-------------+-------+------------------+
| Range_Nilai | Point | Keterangan       |
+-------------+-------+------------------+
| 0 s/d 50    | E     | Tidak Lulus      |
| 51 s/d 80   | C     | Cukup            |
| 81 s/d 100  | A     | Sangat Memuaskan |
+-------------+-------+------------------+

and I want to get my results like this

enter image description here

but my results like this

enter image description here

when I try to change my query like this

THEN (SELECT b.Point,b.Keterangan FROM range_nilai b where b.Range_Nilai='0 s/d 50')

I got error 'Operand should contain 1 column(s)'

can someone help me pls? this my fiddle 

SELECT  a.*,
        ( CASE 
            WHEN a.nilai >=0 && a.Nilai<=50 
            THEN (SELECT Point FROM range_nilai where Range_Nilai='0 s/d 50')
            WHEN a.nilai >50 && a.Nilai<=80 
            THEN (SELECT Point FROM range_nilai where Range_Nilai='51 s/d 80')
            WHEN a.nilai >80 && a.Nilai<=100 
            THEN (SELECT Point FROM range_nilai where Range_Nilai='81 s/d 100')
            ELSE NULL
        END
         ) as Point,
        ( CASE 
            WHEN a.nilai >=0 && a.Nilai<=50 
            THEN (SELECT Keterangan FROM range_nilai where Range_Nilai='0 s/d 50')
            WHEN a.nilai >50 && a.Nilai<=80 
            THEN (SELECT Keterangan FROM range_nilai where Range_Nilai='51 s/d 80')
            WHEN a.nilai >80 && a.Nilai<=100 
            THEN (SELECT Keterangan FROM range_nilai where Range_Nilai='81 s/d 100')
            ELSE NULL
        END
         ) as Keterangan
FROM nilai a

this my last query I have. may someone have more better query than me?

Upvotes: 0

Views: 78

Answers (1)

forpas
forpas

Reputation: 164089

You can join the tables and use the function SUBSTRING_INDEX() in the ON clause:

select n.*, r.Point, r.Keterangan
from nilai n inner join range_nilai r
on n.Nilai 
  between 
    substring_index(r.Range_Nilai, ' s/d ', 1)
    and
    substring_index(r.Range_Nilai, ' s/d ', -1)

See the demo.
Results:

| No  | NIK   | Nama       | Nilai | Point | Keterangan       |
| --- | ----- | ---------- | ----- | ----- | ---------------- |
| 1   | 6001  | Syafruddin | 70    | C     | Cukup            |
| 2   | 20000 | Maman      | 90    | A     | Sangat Memuaskan |
| 3   | 30000 | Linda      | 40    | E     | Tidak Lulus      |
| 4   | 40000 | Tesa       | 71    | C     | Cukup            |

Upvotes: 1

Related Questions