Re: Should I denormalize this?

> 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

-- 
  no sig today