Re: [Q, theory] how to write complicated query?

Igor M. Klinchin wrote:
>
> We have one problem and not understand now is this an our problem
> or the real problem. So two tables
>
> Master
> ------
> pkey,
> name
>
> Detail
> ------
> pkey,
> fkey_master references master(pkey),
> num number -- some positive integer
>
> We need select all pkey's from Master grouping them by
> name
> and equal sets of Detail.num.
>

for example, set of Detail.num is: "NUM in (1,2,3,4)"

• first method: construction HAVING:

select PKEY, NAME from MASTER
group by PKEY, NAME
HAVING PKEY in
(select PKEY

   from DETAIL
  where NUM in (1,2,3,4)
)
and count(*)>=4
/

• second method:

select PKEY, NAME from MASTER

where PKEY in (select PKEY from DETAIL where NUM=1)
and   PKEY in (select PKEY from DETAIL where NUM=2)
and   PKEY in (select PKEY from DETAIL where NUM=3)
and   PKEY in (select PKEY from DETAIL where NUM=4)

• third method: construction INTERSECT:

select PKEY, NAME from MASTER
where PKEY in
(select PKEY from DETAIL where NUM=1
 intersect
 select PKEY from DETAIL where NUM=2
 intersect
 select PKEY from DETAIL where NUM=3
 intersect
 select PKEY from DETAIL where NUM=4
)

Third method is most fast.

Best regards!

Victor Abramov
abr_at_ivcais.msk.ru Received on Fri Sep 13 1996 - 00:00:00 CEST