SQL query need optimizing

muklee · #1 (**permalink**) 11-20-07, 05:50

Hi,

I am having a problem to merge multiple records into 1 single record. Please have a look on the sample data below to have better understanding:

[sample]

Table Name: TBL_STUDENT

SEQ_NO ID_NO ID_TYPE
-------- ------- ---------
1 S11198 01
2 K11198 01
3 Z11198 02

Table Name: TBL_STUDENT_SERV

SEQ_NO SERV
-------- -------
1 A
1 B
2 A
3 C

* SERV can only has 3 values, which is 'A', 'B' or 'C'.

[/sample]

By using the SQL query below I am able to generate the below output, but it will cause a lot of performance issue when records growing larger.

[output]

SEQ_NO ID_NO ID_TYPE SERV_A SERV_B SERV_C
-------- ------- ---------- --------- -------- ---------
1 S11198 01 Y Y N
2 K11198 01 Y N N
3 Z11198 02 N N Y

[/output]

Code:

SELECT C.SEQ_NO, C.ID_NO, C.ID_TYPE, CS.SERV
CASE
    WHEN (SELECT COUNT(1) FROM TBL_STUDENT_SERV CS WHERE CS.SEQ_NO = C.SEQ_NO AND CS.SERV = 'A') > 0 THEN 'Y'
    ELSE 'N'
END AS SERV_A,
CASE
    WHEN (SELECT COUNT(1) FROM TBL_STUDENT_SERV CS WHERE CS.SEQ_NO = C.SEQ_NO AND CS.SERV = 'B') > 0 THEN 'Y'
    ELSE 'N'
END AS SERV_B,
CASE
    WHEN (SELECT COUNT(1) FROM TBL_STUDENT_SERV CS WHERE CS.SEQ_NO = C.SEQ_NO AND CS.SERV = 'C') > 0 THEN 'Y'
    ELSE 'N'
END AS SERV_C
FROM TBL_STUDENT C;

I have totally run out of idea to optimize the SQL above and hope to get help from the experts.

Thank in advanced.

umayer · #2 (**permalink**) 11-20-07, 06:11

try this :

SELECT
A.SEQ_NO , A.ID_NO , A.ID_TYPE, MAX(B.SERV_A), MAX(B.SERV_B), MAX(B.SERV_C)
FROM
TBL_STUDENT A
INNER JOIN

(
SELECT SEQ_NO ,
CASE WHEN SERV='A' THEN 'Y' ELSE 'N' END AS SERV_A ,
CASE WHEN SERV='B' THEN 'Y' ELSE 'N' END AS SERV_B ,
CASE WHEN SERV='C' THEN 'Y' ELSE 'N' END AS SERV_C
FROM TBL_STUDENT_SERV
) B

ON A.SEQ_NO = B.SEQ_NO
GROUP BY A.SEQ_NO , A.ID_NO , A.ID_TYPE

r937 · #3 (**permalink**) 11-20-07, 06:16

Code:

select SEQ_NO 
     , ID_NO 
     , ID_TYPE
     , max(A) as SERV_A
     , max(B) as SERV_B
     , max(C) as SERV_C
  from (
       select S.SEQ_NO 
            , S.ID_NO 
            , S.ID_TYPE
            , case when SS.SERV = 'A'
                   then 'Y' 
                   else 'N' end as A
            , case when SS.SERV = 'B'
                   then 'Y' 
                   else 'N' end as B
            , case when SS.SERV = 'C'
                   then 'Y' 
                   else 'N' end as C
         from TBL_STUDENT as S
       left outer
         join TBL_STUDENT_SERV as SS
           on SS.SEQ_NO = S.SEQ_NO
       ) as d
group
    by SEQ_NO     
     , ID_NO      
     , ID_TYPE

i believe the LEFT OUTER JOIN is an essential part of the solution

muklee · #4 (**permalink**) 11-20-07, 22:09

Great!!!!!! Both queries are working fine for me. Thanks guys

Internet Services

Web Development

Digital Marketing

Consumer Tech