Select Statement?! (Oracle 9i)

From: Emanuel Blaser <emanuelblaser_at_yahoo.com>
Date: 4 Jun 2004 06:08:36 -0700
Message-ID: <857de1e3.0406040508.1378bf4f@posting.google.com>

Hi

Need some help creating a sql statement the best way.

Description:
I have one table (T1) with three foreing keys to table T2, T3, T4.

If only one of the three relation is good (doesen't matter wich) i want do get a result. I can't check only the fields in table T1 cause it's possible that there are deadlinks and also every table has a deleteflag, if it's set i have to ignore the row)

How i would do this:
select * from t1,t2,t3,t4

where t1.t2id = t2.id (+) and t2.deleteflag (+) <> 1  
  and t1.t3id = t3.id (+) and t3.deleteflag (+) <> 1
  and t1.t4id = t4.id (+) and t4.deleteflag (+) <> 1   
  and (
      exists (select 'x' from t2 
              where t1.t2id = t2.id and t2.deleteflag <> 1) 
  or  exists (select 'x' from t3 
              where t1.t3id = t3.id and t3.deleteflag <> 1)
  or  exists (select 'x' from t4 
              where t1.t4id = t4.id and t4.deleteflag <> 1)

  )
  and t1.deleteflag <> 1

Maybe this is the best way to do it but i don't think so.

Thanks for your help
Emanuel Received on Fri Jun 04 2004 - 08:08:36 CDT