Re: Should I denormalize this?

The INTERSECT operator should do the trick:

• Case numbers that have Shell and Arco in them... SELECT case FROM case_table WHERE defendant = 'Shell' INTERSECT SELECT case FROM case_table WHERE defendant = 'Arco';

etc.

Rene Nyffenegger <rene.nyffenegger_at_gmx.ch> wrote in message news:<b0en1p$o15nf$1_at_ID-82536.news.dfncis.de>...
> > I am looking at this problem and I don't know how to handle it other
> > than de-normalizing the table. Here is a simple table:
> >
> > Case # Defendant
> > -------- --------------
> > 123 Shell
> > 123 Arco
> > 123 Exxon
> >
> >
> > Here is how I need the results to be in two different scenarios:
> > Scenario #1) "Bring me all case numbers that have Shell AND Arco in
> > them." Here, it would output case # 123
> > Scenario #2) "Bring me all case numbers that have Shell AND Union 76 in
> > them." Here, it should return an empty set since Union 76 is not a
> > company associated with any case.
> >
> > I am not sure how to handle this. I was thinking about de-normalizing teh
> > table and listing one record per case and all the defendants will be
> > listed in a single field separated by a token like: "Shell~Arco~Exxon" and
> > then doing a SELECT Case WHERE Defendant LIKE "Arco" AND Defendant LIKE
> > "Shell" or whatever, but I am wondering if there is a more elegant way.
> >
> > - Steve
>
> Steve,
>
> have a table containing the cases (master table) and
> a table containing the defendants for a case (detail table or child table).
>
> Join then the defendants table twice to the case table, as shown below:
>
>
> create table case_ (
> id number primary key
> );
>
>
> create table defendant_ (
> id references case_,
> defendant varchar2(30)
> );
>
> insert into case_ values (122);
> insert into case_ values (123);
> insert into case_ values (124);
>
> insert into defendant_ values(122,'Arco');
> insert into defendant_ values(122,'Union 76');
>
> insert into defendant_ values(123,'Shell');
> insert into defendant_ values(123,'Arco');
> insert into defendant_ values(123,'Exxon');
>
> insert into defendant_ values(124,'BP');
> insert into defendant_ values(124,'Exxon');
>
>
> prompt Scenario 1
>
> select
> c.id
> from
> case_ c,
> defendant_ d1,
> defendant_ d2
> where
> d1.id = c.id and
> d2.id = c.id and
> d1.defendant = 'Shell' and
> d2.defendant = 'Arco';
>
> prompt Scenario 2
>
> select
> c.id
> from
> case_ c,
> defendant_ d1,
> defendant_ d2
> where
> d1.id = c.id and
> d2.id = c.id and
> d1.defendant = 'Shell' and
> d2.defendant = 'Union 76';
>
> drop table defendant_;
> drop table case_;
>
>
>
> Rene
Received on Tue Jan 21 2003 - 06:32:56 CST