Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2004
    Posts
    24

    Unanswered: help with left outer join

    Hi,

    I am using left outer join and getting the all the matching records from 2 tables and getting additional rows from table 1.
    But I just want the rows which are not matching. I cannot user not in because I am matching multiple columms between table 1 and table 2
    Here is my query for left outer join


    select a.*,b.*
    from diff_2003Q1 a
    left outer join fas140_unam_factr_temp_Q1 b
    on a.cd_amtn_per=case (b.cd_amtn_per) when '15/20 YEAR' then '15 YEAR'
    when '30 YEAR' then '30 YEAR'
    when 'OTHER' then 'OTHER'
    end
    and a.cd_acctg_dsgntn_lnlvl=b.cd_acctg_dsgntn_lvl
    and a.rate_pc_cpn_amtn=b.rate_pc_cpn_amtn
    and a.amt_dlvy_fee=b.amt_dlvy_fee
    and b.dt_eop_qtr='20030331'
    and CASE substring(a.dt_fundg_qtr,5,6)
    when 'Q1'
    THEN substring(a.dt_fundg_qtr,1,4) + '0331'
    when 'Q2'
    THEN substring(a.dt_fundg_qtr,1,4) + '0630'
    when 'Q3'
    THEN substring(a.dt_fundg_qtr,1,4) + '0930'
    when 'Q4'
    THEN substring(a.dt_fundg_qtr,1,4) + '1231'
    End = b.dt_fundg_qtr
    and a.amt_pch_upb=b.amt_pch_upb
    order by b.cd_amtn_per

    If any one can tell me how to get this in Sybase that would be great.

    Thanks
    Srinivas
    Thanks
    Srinivas chityala

  2. #2
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    Wouldn't that be a NOT EXISTS then?
    Thanks,

    Matt

  3. #3
    Join Date
    Feb 2004
    Posts
    24
    Thanks Matt,
    Yes it is NOT EXISTS
    I am not using left outer join, I am using simple join with NOT EXISTS

    Now my query looks like

    select *
    from diff_2003Q1 a
    where not exists(
    select b.amt_pch_upb
    from fas140_unam_factr_temp_Q1 b
    where a.cd_amtn_per=case (b.cd_amtn_per) when '15/20 YEAR' then '15 YEAR'
    when '30 YEAR' then '30 YEAR'
    when 'OTHER' then 'OTHER'
    end
    and a.cd_acctg_dsgntn_lnlvl=b.cd_acctg_dsgntn_lvl
    and a.rate_pc_cpn_amtn=b.rate_pc_cpn_amtn

    and b.dt_eop_qtr='20030331'
    and CASE substring(a.dt_fundg_qtr,5,6)
    when 'Q1'
    THEN substring(a.dt_fundg_qtr,1,4) + '0331'
    when 'Q2'
    THEN substring(a.dt_fundg_qtr,1,4) + '0630'
    when 'Q3'
    THEN substring(a.dt_fundg_qtr,1,4) + '0930'
    when 'Q4'
    THEN substring(a.dt_fundg_qtr,1,4) + '1231'
    End = b.dt_fundg_qtr

    and a.amt_dlvy_fee=b.amt_dlvy_fee
    and a.amt_pch_upb=b.amt_pch_upb
    )



    Originally posted by MattR
    Wouldn't that be a NOT EXISTS then?
    Thanks
    Srinivas chityala

  4. #4
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    I think it would be the opposite way.

    You want rows from table 2 (fas140_unam_factr_temp_Q1) where rows do not exist in table 1 (diff_2003Q1),

    e.g. (something like this)
    Code:
    SELECT b.amt_pch_upb
      FROM fas140_unam_factr_temp_Q1 b
     WHERE NOT EXISTS ( SELECT *
                          FROM diff_2003Q1 a
                         WHERE a.cd_amtn_per = CASE ( b.cd_amtn_per )
                                                    WHEN '15/20 YEAR' THEN '15 YEAR'
                                                    ELSE b.cd_amtn_per 
                                               END
                           AND a.cd_acctg_dsgntn_lnlvl = b.cd_acctg_dsgntn_lvl
                           AND a.rate_pc_cpn_amtn      = b.rate_pc_cpn_amtn
                           AND b.dt_eop_qtr            = '20030331'
                           AND CASE substring ( a.dt_fundg_qtr, 5 , 6 )
                                    WHEN 'Q1'
                                      THEN substring ( a.dt_fundg_qtr, 1, 4 ) + '0331'
                                    WHEN 'Q2'
                                      THEN substring ( a.dt_fundg_qtr, 1, 4 ) + '0630'
                                    WHEN 'Q3'
                                      THEN substring ( a.dt_fundg_qtr, 1, 4 ) + '0930'
                                    WHEN 'Q4' 
                                      THEN substring ( a.dt_fundg_qtr, 1, 4 ) + '1231' 
                               END = b.dt_fundg_qtr
                           AND a.amt_dlvy_fee = b.amt_dlvy_fee
                           AND a.amt_pch_upb  = b.amt_pch_upb )
    Thanks,

    Matt

  5. #5
    Join Date
    Feb 2004
    Posts
    24
    No Mattr
    in diff_2003Q1 has more records then fas140_unam_factr_temp_Q1.
    So I got the correct results.

    Thanks
    Srinivas

    Originally posted by MattR
    I think it would be the opposite way.

    You want rows from table 2 (fas140_unam_factr_temp_Q1) where rows do not exist in table 1 (diff_2003Q1),

    e.g. (something like this)
    Code:
    SELECT b.amt_pch_upb
      FROM fas140_unam_factr_temp_Q1 b
     WHERE NOT EXISTS ( SELECT *
                          FROM diff_2003Q1 a
                         WHERE a.cd_amtn_per = CASE ( b.cd_amtn_per )
                                                    WHEN '15/20 YEAR' THEN '15 YEAR'
                                                    ELSE b.cd_amtn_per 
                                               END
                           AND a.cd_acctg_dsgntn_lnlvl = b.cd_acctg_dsgntn_lvl
                           AND a.rate_pc_cpn_amtn      = b.rate_pc_cpn_amtn
                           AND b.dt_eop_qtr            = '20030331'
                           AND CASE substring ( a.dt_fundg_qtr, 5 , 6 )
                                    WHEN 'Q1'
                                      THEN substring ( a.dt_fundg_qtr, 1, 4 ) + '0331'
                                    WHEN 'Q2'
                                      THEN substring ( a.dt_fundg_qtr, 1, 4 ) + '0630'
                                    WHEN 'Q3'
                                      THEN substring ( a.dt_fundg_qtr, 1, 4 ) + '0930'
                                    WHEN 'Q4' 
                                      THEN substring ( a.dt_fundg_qtr, 1, 4 ) + '1231' 
                               END = b.dt_fundg_qtr
                           AND a.amt_dlvy_fee = b.amt_dlvy_fee
                           AND a.amt_pch_upb  = b.amt_pch_upb )
    Thanks
    Srinivas chityala

  6. #6
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    Oh ok, well, glad I could help!
    Thanks,

    Matt

  7. #7
    Join Date
    Feb 2004
    Location
    Chester Springs, PA
    Posts
    16

    Finding Nonmatch in Outer Join

    The case is select a.* from a left outer join b, and if I am reading this correctly you want to find the records that are in a that have no corresponding match in b

    The most simple way to do this is to write the query as such...

    select a.id, a.col1,...a.coln
    from a left outer join b
    on a.joinCol = b.joinCol (or whatever the join condition is)
    where b.id is null (you can use any column from b)

    Obviously this is more simplistic than your join condition, but I think the idea still holds.

    You can also join on an inequality, but I've not found that to be a good way to write queries of good performance.

  8. #8
    Join Date
    Feb 2004
    Posts
    24

    Re: Finding Nonmatch in Outer Join

    Yes, you are right. It is not a good idea to write the query with not exist or not in. Your query is a good help for me.

    Thanks
    Srinivas
    Originally posted by dbslave
    The case is select a.* from a left outer join b, and if I am reading this correctly you want to find the records that are in a that have no corresponding match in b

    The most simple way to do this is to write the query as such...

    select a.id, a.col1,...a.coln
    from a left outer join b
    on a.joinCol = b.joinCol (or whatever the join condition is)
    where b.id is null (you can use any column from b)

    Obviously this is more simplistic than your join condition, but I think the idea still holds.

    You can also join on an inequality, but I've not found that to be a good way to write queries of good performance.
    Thanks
    Srinivas chityala

Posting Permissions

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