Re: help with a simple query

You can try:

select a.*
from a, b
where ((a.col1 is not null and a.col1 = val1) or (a.col1 is null and b.col1 = val1))
and a.col2 = b.col2

or shorter:

select a.*
from a, b
where nvl(a.col1, b.col1) = val1
and a.col2 = b.col2

If table b can be empty, you can use :

select *
from a
where (a.col1 is not null and a.col1 = val1) or (a.col1 is null and exists(
  select 1
  from b
  where b.col2 = a.col2
  and b.col1 = val1)

or

select a.*
from a, b
where nvl(a.col1, b.col1) = val1
and a.col2 = b.col2(+)

Marc

>>>>>>>>>>>>>>>>>> Oorspronkelijk bericht <<<<<<<<<<<<<<<<<<

Op 2000-12-18, 22:47:26, schreef javadrink_at_my-deja.com over het thema

help with a simple query:

> I need to do a following query (using table a, b):

> select *
> from a
> where a.col1=val1 or if a.col1=null then b.col1=val1
> wherea.col2 = b.col2
 

> how to express the if part using SQL? thanks a lot!
 

> (basically what I want to do is: selection based tableA column1, but i
 f
> tableA column1 is null, join tableA with tableB using column2 and make
 

> sure tableB column 1 equal to the input value)
 

> Thanks!

> Sent via Deja.com
> http://www.deja.com/
Received on Tue Dec 19 2000 - 00:40:05 CST