Output Group Concat with Conditional Statement

Question

I Have sql table like this:

Table A

NIP        BIDANG      MAYOR       MINOR
1            A          ZZ          YY
2            A          ZZ          XX
2            A                      YY

and i have another table :

Table B

NIP        Keahlian     Nilai
1             XX          2
1             YY          0
1             ZZ          3
2             XX          4
2             YY          2
2             ZZ          3

Table C
NIP           Score       Limit
1               10          15
2               20          15

Based on that table, i want to create a table with conditional statement like this : if socre <= limit, than saran = xxx else saran = group concat from mayor minor but sorted by the high value in coloumn Nilai in Table B. So, the output like this :

NIP       Saran
1          XXX
2        XX, ZZ, YY

How to make it, please help me.

It seems that data in Table A is not necessary to produced the required output. Is that correct? — Joseph B, Apr 23 '14 at 13:19
well its possible to do but your first table is irrelevant in this case , since the data you want could be gathered from last 2 tables. — Abhik Chakraborty, Apr 23 '14 at 13:30
Table A is necessary, when keahlian in table B have Nilai 0. that keahlian remains displayed as long as there is keahlian in mayor minor for that NIP. — user3440030, Apr 23 '14 at 13:30
@user3440030 Cold you please explain how the above rule would be applied if Table C had Score=15, Limit=10 for NIP=1? — Joseph B, Apr 23 '14 at 13:34
@user3440030 Thank you for the clarification. See the modified query in my answer below. — Joseph B, Apr 23 '14 at 13:59

score 0 · Answer 1 · edited May 23 '17 at 12:11

The following query gets you the required output. You can use ORDER BY with the GROUP_CONCAT function. Also, Limit is a reserved word, so I renamed the column as Limitx. Please see this SQL Fiddle.

SELECT
    NIP,
    CASE SIGN(Score-Limitx)
        WHEN -1 THEN 'XXX'
        ELSE (SELECT GROUP_CONCAT(b.Keahlian ORDER BY b.Nilai DESC) FROM TableB b WHERE b.NIP = c.NIP)
    END Saran
FROM
    TableC c;

EDIT:

Here is the modified query, based on the requirement that Keahlian must exist in either MAYOR or MINOR column of TableA:

SELECT
    NIP,
    CASE SIGN(Score-Limitx)
        WHEN -1 THEN 'XXX'
        ELSE (SELECT 
                GROUP_CONCAT(b.Keahlian ORDER BY b.Nilai DESC) 
              FROM TableB b
              INNER JOIN
              (SELECT NIP, MAYOR Keahlian
               FROM TableA
               UNION ALL
               SELECT NIP, MINOR
               FROM TableA 
              ) TableA_list
              ON TableA_list.NIP = b.NIP AND TableA_list.Keahlian = b.Keahlian
              WHERE b.NIP = c.NIP
              )
    END Saran
FROM
    TableC c;

Updated SQL Fiddle

References:

Output Group Concat with Conditional Statement

1 Answers1