Results 1 to 13 of 13

Thread: Self join

  1. #1
    Join Date
    Aug 2012
    Posts
    5

    Unanswered: Self join

    Good Morning

    I am totally confused as to how i can use self join

    I have a table which holds profile information.There are some standard profiles and user specific profiles.

    Sample data from the table

    ProfileNo StandardProfileNo Type Risk
    100 5 I 0
    250 6 I 0
    350 0 I 0
    .....
    5 0 N 1
    6 0 N 2

    Conditions to fetch the data frm this table are
    1)if user specific profile has standard profile > 0 , fetch Type and Risk of corresponding to standard profile..
    2)If user specific profile has standard profile with value 0 , fetch user specific profile's Risk and Type.

    I have tried something like this

    select a.ProfileNo ,a.StandardProfileNo ,b.Type,b.Risk from Profile a , Profile b where a.StandardProfileNo = b.ProfileNo

    The returned rows are
    ProfileNo StandardProfileNo Type Risk
    100 5 N 1
    250 6 N 2

    But I want the Output to be

    ProfileNo StandardProfileNo Type Risk
    100 5 N 1
    250 6 N 2
    350 0 I 0

    How should i include the condition to fetch profiles with standard profile = 0
    in the query.Please help me.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Why are ProfileNo = 5 and 6 not included in Output?

    In other words, How to distinguish standard profiles and user specific profiles?

    Anyway, consider to use OUTER JOIN.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    3)If user specific profile has standard profile > 0 and there was no corresponding standard profile,
    Then What result(output) do you want?

  4. #4
    Join Date
    Aug 2012
    Posts
    5
    Thank you tonkuma for the reply.

    Related to your question as to why ProfileNo 5 and 6 are ignored in the output,
    I run the query only for user specific profiles . Forgot to mention this

    For all user specific profiles which have standard profile > 0 the corresponding standard profile always exists.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How to distinguish standard profiles and user specific profiles?

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by tonkuma View Post
    How to distinguish standard profiles and user specific profiles?
    I guessed some possibilities from your sample data and required output...
    Sample data from the table

    ProfileNo StandardProfileNo Type Risk
    100 5 I 0
    250 6 I 0
    350 0 I 0
    .....
    5 0 N 1
    6 0 N 2
    But I want the Output to be

    ProfileNo StandardProfileNo Type Risk
    100 5 N 1
    250 6 N 2
    350 0 I 0

    (1) user specific profile : Type='I', standard profile : Type='N'
    (2) user specific profile : ProfileNo >= 100, standard profile : ProfileNo < 100
    (3) user specific profile : not in any other row's StandardProfileNo, standard profile : referenced in some other row's StandardProfileNo

    But, too little sample data made it difficult to guess rational way to distinguish standard profiles and user specific profiles.
    Last edited by tonkuma; 08-29-12 at 07:34. Reason: Add reference to output.

  7. #7
    Join Date
    Nov 2011
    Posts
    334
    ok , if you mean if a use specific profile have a matched standard profile no
    then pick up standard profile risk and type
    else pick up the use specific profile itself。
    maybe you can use this
    Code:
    with profiletable (profileno,standardprofileno,type,risk) as (
    values ( 100, 5, 'I', 0 )
          ,( 250, 6, 'I', 0 )
          ,( 350, 0, 'I', 0 )
          ,( 5, 0, 'N', 1 )
          ,( 6, 0, 'N', 2 ) )
    select p1.profileno,p1.standardprofileno,
           coalesce(p2.type,p1.type),coalesce(p2.risk,p1.risk)
       from profiletable p1 left join profiletable p2
    on p1.standardprofileno = p2.profileno
    and if your want to exclude startard profile
    ( i guess which is the profile that have a user specific profile matched with it )
    maybe you can use thie
    Code:
    with profiletable (profileno,standardprofileno,type,risk) as (
    values ( 100, 5, 'I', 0 )
          ,( 250, 6, 'I', 0 )
          ,( 350, 0, 'I', 0 )
          ,( 5, 0, 'N', 1 )
          ,( 6, 0, 'N', 2 ) )
    select p1.profileno,p1.standardprofileno,
           coalesce(p2.type,p1.type),coalesce(p2.risk,p1.risk)
       from profiletable p1 left join profiletable p2
    on p1.standardprofileno = p2.profileno
    where not exists (
       select 0 from profiletable p3 
       where p1.profileno =  p3.standardprofileno )

  8. #8
    Join Date
    Aug 2012
    Posts
    5
    @Tonkuma
    You are almost correct on your guess

    Points 1 and 3 are true about user specific profiles and standard profiles.
    Standard profiles are identifed with type as 'I' and user specific profiles are identified withtype as 'N'. But i dont use the Type condition check in the where clause to distinguish between them, instead i use profileNo condition check in where clause of the query(where profileNo in (user specific profiles)).


    @Fengsun2
    Thank you for your reply.That worked

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    But i dont use the Type condition check in the where clause to distinguish between them,
    Why?
    "Type condition check" must be effective(less expensive) than "profileNo in (user specific profiles)".

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by venkat84 View Post
    ...
    But i dont use the Type condition check in the where clause to distinguish between them, instead i use profileNo condition check in where clause of the query(where profileNo in (user specific profiles)).
    An issue in this way might be (though this might be not your situation/requirement) that
    if inserted standard profiles first, then later insert user specific profiles which reference some of standard profiles,
    unreferenced standard profiles would be included in output like...
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     profile_table
    ( profile_no , standard_profile_no , type , risk ) AS (
    VALUES
      ( 100 , 5 , 'I' , SMALLINT(0) )
    , ( 250 , 6 , 'I' , SMALLINT(0) )
    , ( 350 , 0 , 'I' , SMALLINT(0) )
    , (   5 , 0 , 'N' , SMALLINT(1) )
    , (   6 , 0 , 'N' , SMALLINT(2) )
    , (   7 , 0 , 'N' , SMALLINT(3) )
    )
    SELECT u.profile_no
         , u.standard_profile_no
         , COALESCE(s.type , u.type) AS type
         , COALESCE(s.risk , u.risk) AS risk
     FROM  profile_table u /*user profile    */
     LEFT  OUTER JOIN
           profile_table s /*standard profile*/
      ON   s.profile_no = u.standard_profile_no
     WHERE NOT EXISTS (
           SELECT 0
            FROM  profile_table e /*existence checking*/
            WHERE e.standard_profile_no = u.profile_no
           )
    ;
    ------------------------------------------------------------------------------
    
    PROFILE_NO  STANDARD_PROFILE_NO TYPE RISK  
    ----------- ------------------- ---- ------
            100                   5 N         1
            250                   6 N         2
            350                   0 I         0
              7                   0 N         3
    
      4 record(s) selected.

    But if type column was utilized, no need consider such issue and query might be simplified like ...
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     profile_table
    ( profile_no , standard_profile_no , type , risk ) AS (
    VALUES
      ( 100 , 5 , 'I' , SMALLINT(0) )
    , ( 250 , 6 , 'I' , SMALLINT(0) )
    , ( 350 , 0 , 'I' , SMALLINT(0) )
    , (   5 , 0 , 'N' , SMALLINT(1) )
    , (   6 , 0 , 'N' , SMALLINT(2) )
    , (   7 , 0 , 'N' , SMALLINT(3) )
    )
    SELECT u.profile_no
         , u.standard_profile_no
         , COALESCE(s.type , u.type) AS type
         , COALESCE(s.risk , u.risk) AS risk
     FROM  profile_table u /*user profile    */
     LEFT  OUTER JOIN
           profile_table s /*standard profile*/
      ON   s.profile_no = u.standard_profile_no
    /* AND s.type       = 'N' */
     WHERE u.type       = 'I'
    ;
    ------------------------------------------------------------------------------
    
    PROFILE_NO  STANDARD_PROFILE_NO TYPE RISK  
    ----------- ------------------- ---- ------
            100                   5 N         1
            250                   6 N         2
            350                   0 I         0
    
      3 record(s) selected.

    I want to ask again
    Quote Originally Posted by tonkuma View Post
    Why?
    "Type condition check" must be effective(less expensive) than "profileNo in (user specific profiles)".
    for your statements
    Quote Originally Posted by venkat84 View Post
    ...
    But i dont use the Type condition check in the where clause to distinguish between them, instead i use profileNo condition check in where clause of the query(where profileNo in (user specific profiles)).

    ...
    Last edited by tonkuma; 08-29-12 at 16:23. Reason: Add last quote of venkat84.

  11. #11
    Join Date
    Aug 2012
    Posts
    5
    Let me explain u briefly why i use profileNo in where clause.

    Our's is a trading application where we maintain accounts , profiles , funds ,profiledistribution and accountholdings in tables called ACCOUNT,PROFILE,FUND, PROFILEDISTRIBUTION and ACCOUNTPORTFOLIO respectively.

    PROFILE table is linked to ACCOUNT table by profileNo.PROFILEDISTRIBUTION table is linked to PROFILE table by profileNo.ACCOUNT and ACCOUNTPORTFOLIO are linked by accountNo

    Every account will have specific profile(which we call user specific profile).

    If a user specific profile has standard profile >0 then we look for standard profile in profile distribution table.
    If a user specific profile has standard profile =0 then we look for user specific profile in profile distribution table.

    In my scenario i will be given list of accounts and i have to find out the funds into which these accounts invest into and what are their holdings.Along with this i should also show some profile related info in the output.Since for a given account i can get the profileNo and based on the profileNo i can fetch it's information from PROFILE table,i use profileNo in the where clause.

    What u meant by 'Type check must be effective(less expensive)'.Does it have anything to do with COST of the query.

  12. #12
    Join Date
    Nov 2011
    Posts
    334
    You can use the db2expln to get the cost of the query.
    like this:
    db2expln -d <dbname> -q "the query" -t -g

  13. #13
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I tried to construct a query from your information.

    (1)
    -- i will be given list of accounts
    Code:
     FROM  account  AS ac
    
     WHERE ac.accountNo IN (list of accounts)
    (2)
    -- and i have to find out the funds into which these accounts invest into and what are their holdings.

    This may be not related directly to issues in this thread.
    So, I will skip now.


    (3)
    -- PROFILE table is linked to ACCOUNT table by profileNo.
    -- for a given account i can get the profileNo and based on the profileNo i can fetch it's information from PROFILE table
    -- Every account will have specific profile(which we call user specific profile)
    Code:
     FROM  account  AS ac
     INNER JOIN
           profile  AS pf /* user specific profile */
      ON   pf.profileNo = ac.profileNo
    
     WHERE ac.accountNo IN (list of accounts)
    (4)
    -- If a user specific profile has standard profile >0 then we look for standard profile in profile distribution table.
    -- If a user specific profile has standard profile =0 then we look for user specific profile in profile distribution table.
    -- PROFILEDISTRIBUTION table is linked to PROFILE table by profileNo
    Code:
     FROM  account             AS ac
     INNER JOIN
           profile             AS pf /* user specific profile */
      ON   pf.profileNo = ac.profileNo
     LEFT  OUTER JOIN
           profiledistribution AS ps /* standard profile */
      ON   pf.standard_profileNo > 0
       AND ps.profileNo = pf.standard_profileNo
     LEFT  OUTER JOIN
           profiledistribution AS pu /* user specific profile */
      ON   pf.standard_profileNo = 0
       AND pu.profileNo = pf.profileNo
    
     WHERE ac.accountNo IN (list of accounts)
    (5)
    -- show some profile related info in the output
    Code:
    SELECT ac.accountNo
         , pf.profileNo
         , pf.standard_profileNo
    ...
         , COALESCE(ps.type , pu.type) AS type
         , COALESCE(ps.risk , pu.risk) AS risk
    ...
    
     FROM  account             AS ac
     INNER JOIN
           profile             AS pf /* user specific profile */
      ON   pf.profileNo = ac.profileNo
     LEFT  OUTER JOIN
           profiledistribution AS ps /* standard profile */
      ON   pf.standard_profileNo > 0
       AND ps.profileNo = pf.standard_profileNo
     LEFT  OUTER JOIN
           profiledistribution AS pu /* user specific profile */
      ON   pf.standard_profileNo = 0
       AND pu.profileNo = pf.profileNo
    
     WHERE ac.accountNo IN (list of accounts)
    (6)
    -- i use profileNo in the where clause.
    It might be unnecessary to use profileNo in the where clause.
    Because, already user specific profile and related standard profile(if exists) were got.
    Last edited by tonkuma; 08-30-12 at 02:27.

Posting Permissions

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