Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2012
    Posts
    4

    Unanswered: Case or If in where Condition in SYBASE ASE

    I have 3 tables which I am joining in which 2 tables i am cross joining.

    The problem is all are inner join, but some time 1 condition can come NULL so I wanna put 1 NULL and 2nd value. pls see the query
    Code:
    select 
    cr.id
    , cm.orgaparty_uri
    , cm.role_uri
    , am.orgaparty_uri
    , am.role_uri
    , gu.name_t
    , gu1.name_t
    from 
    cdm_clientrelation cr
    ,cdm_commercial_roles cm
    ,cdm_commercial_roles am
    ,gcl_user gu
    ,gcl_user gu1
    where
    cm.agreement_uri ='cdm:clientRelation/'+cr.id
    and  cm.role_uri ='gcl:role/CommercialManager'
    and cm.orgaparty_uri= 'gcl:user/'+gu.id 
    AND am.agreement_uri='cdm:clientRelation/'+cr.id
    AND am.role_uri='gcl:role/AdministrativeManager'
    AND am.orgaparty_uri = 'gcl:user/'+gu1.id
    some time Might be possible that either one of the row is not there in cdm_commercial_roles table for Commercial or Admin Role . so it wont get it and inner join fail but I need if the Row is not there it will give as NULL as my select Statement name awaiting for ur reply

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    left join to cdm_commercial_roles

  3. #3
    Join Date
    Jan 2012
    Posts
    4
    But There might be case,

    It has commercial Manager but Dont have Admin

    or Have admin but Dont have Commercial manager.

    Can you pls explain how to do it. I am not very strong in DB

    and left join which one CM or AM

  4. #4
    Join Date
    Jan 2012
    Posts
    4
    left join wont Help, problem is with AND , coz its enforcing for Inner Join

    can u pls write 4 lines how do i make Left Join here

  5. #5
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Sounds like you want a full outer join.
    You can do this with a "union" between a left join and a right join
    OR
    "union all" a left join with a not exist query

    e.g.
    select a.col1, a.col2, b.col2
    from a
    left join b
    on a.col1 = b.col1
    union all
    select b.col1, null, b.col2
    from b
    where not exists
    (select 1 from a where a.col1 = b.col1)

    Not sure how the cross join will fit into all of this as I don't know what your data looks like.

  6. #6
    Join Date
    Jan 2012
    Posts
    4
    Sorry, That wouldn't solve my problem.

    I have sent you my data thro msg.

  7. #7
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Having a quick look at your data it seems you only need to use left joins
    It might be beneficial to other to show some sample/dummy data and your expected output using the sample data.
    I also suggest you read up on database normalization

  8. #8
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Here are some sample data and a left join query that produce the result I think you want
    Code:
    select * into #t1 from (select 
    1, 'aaa' union all select 
    2, 'bbb' union all select 
    3, 'ccc')t1(id,nm)
    
    select * into #t2 from (select
    1, 1, 2 union all select 
    2, 1, 2 union all select 
    3, 1, 3 union all select 
    1, 3, 3 union all select 
    2, 2, 1 union all select
    3, 3, 2)t2(roleid, clid, userid)
    
    select * into #t3 from (select
    1, 'xxa' union all select
    2, 'xxb' union all select
    3, 'xxc')t3(id,nm)
    
    select cm.clid, cm.roleid, am.clid, am.roleid, gu.nm, gu1.nm
    from #t3 cr
    left join #t2 cm
      on cm.clid=cr.id
     and cm.roleid=1
    left join #t2 am
      on am.clid=cr.id
     and am.roleid=2
    left join #t1 gu
      on cm.userid=gu.id
    left join #t1 gu1
      on am.userid=gu1.id

Tags for this Thread

Posting Permissions

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