How to add an and condition to the query only when the column is not null?

Question

I have the below query which is giving correct output when the t2.date is not null

select t1.name,t1.work,t2.type from t1,t2 where t.name=t2.name and t1.postdate < t2.date

But when the t2.date is null the query is not fetching any rows. So I want to add the t1.postdate < t2.datecondition only when the t2.date is not null.

When the t2.date is null I don't want to add it to the query(I want the output as the above query gives without and condition)

select t1.name,t1.work,t2.type from t1,t2 where t.name=t2.name

Please help me with this. Thanks in advance.

So what if I find both a record with a matching date and one with date null for a name? Do you want to join both? Or only the one with the date? With SQL questions it is always advisable to show some sample data and expected result. You are using an out-dated join syntax by the way. — Thorsten Kettner
– Thorsten Kettner, Commented Dec 11, 2015 at 9:16

mns45 · Accepted Answer · 2015-12-11 09:13:01Z

3

I agree to @jarlh, you can use this maybe;

select t1.name,t1.work,t2.type from t1,t2 where t.name=t2.name and (t2.date is null or t1.postdate < t2.date)

answered Dec 11, 2015 at 9:13

mns45

1037 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mathias Magnusson · Accepted Answer · 2015-12-11 10:23:41Z

0

Why not uise NVL to make it clearer and shorter?

select t1.name
      ,t1.work
      ,t2.type 
  from t1 
      ,t2 
 where t.name = t2.name 
   and t1.postdate < nvl(t2.date, t1.postdate + 1);

answered Dec 11, 2015 at 10:23

Mathias Magnusson

1971 silver badge6 bronze badges

Collectives™ on Stack Overflow

How to add an and condition to the query only when the column is not null?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related