Case or If in where Condition in SYBASE ASE

aksar · #1 (**permalink**) 01-04-12, 23:29

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

pdreyer · #2 (**permalink**) 01-05-12, 09:24

left join to cdm_commercial_roles

aksar · #3 (**permalink**) 01-05-12, 09:29

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

aksar · #4 (**permalink**) 01-05-12, 09:31

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

pdreyer · #5 (**permalink**) 01-09-12, 09:04

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.

aksar · #6 (**permalink**) 01-09-12, 20:24

Sorry, That wouldn't solve my problem.

I have sent you my data thro msg.

pdreyer · #7 (**permalink**) 01-11-12, 09:06

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

pdreyer · #8 (**permalink**) 01-11-12, 16:15

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

Internet Services

Web Development

Digital Marketing

Consumer Tech