CODEWITHSUNDEEP
sqlpostgresql
neoo
neoo

Reputation: 181

Case statement for join condition

Hi I want to use case statement in where condition or some similar logic. I want to ignore where condition when there are no rows in tmp_collaboration table and use the condition when table has some rows in it.

Select clbid from tmp_collaboration;

select customer_id, product_id ,clbid 
from customers c
    where hdr_id = 10
          and clbid in (select clbid from tmp_collaboration)
          and status = 'y';

Upvotes: 0

Views: 74

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133370

why not use a JOIN .. if there are rows in table the rows are involved otherwise not . 

select customer_id, product_id ,clbid 
from customers c
INNER JOIN tmp_collaboration t on t.clbid = c.clbid
    AND  hdr_id = 10
    AND status = 'y';

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269973

Is this what you want?

select customer_id, product_id ,clbid 
from customers c
where hdr_id = 10 and status = 'y' and
      (clbid in (select clbid from tmp_collaboration) or
       not exists (select 1 from tmp_collaboration)
      );

Upvotes: 2

Related Questions