Re: Normalizing vs de-normalizing

"Steve Quezadas" <steveeq2_at_tripperjones.com> wrote in message news:xbRU9.9206$Dq.949361_at_newsread2.prod.itd.earthlink.net...
> I have a database with a [Cases] table and a [Defendants] table.
> There are usually about 40 defendants or so for every case. Here's
> the deal though: Sometimes I need to pull up a query that says
> "show me all casenumbers where the defendant can be (Arco or Shell or
> Exxon) AND the casenumber has to have defendant Union76.
>
>
> Do you think this is the best method or is there a better way?

Does something like

select ..
from cases c, defendants d,(select casenumber from defendants where defendant='Union76') U
where c.casenumber(+)=d.casenumber (you want to return allow for Union76 to be the only defendant NB sod's law says I have the (+) on the wrong side) and c.casenumber=u.casenumber
and d.defendant in (list of other defendants)

work?

--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer

******************************************