Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2007
    Posts
    13

    Unanswered: SQL query need optimizing

    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.

  2. #2
    Join Date
    Dec 2005
    Posts
    273
    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

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Nov 2007
    Posts
    13
    Great!!!!!! Both queries are working fine for me. Thanks guys


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •