At 01:23 AM 5/01/2006 +0100, you wrote:

>There are a two possible (client) solutions:
>1)
>select distinct(PCID),
>PCPAID,
>PCMPID,
>PCNR,
>PCNAME1,
>(select PANAME from PRODUCTAREAS where
> PAID=PRODUCTS.PCPAID) as PANAME,
>(select MPNAME from MAINPRODUCTGROUPS where
> MPID=PRODUCTS.PCMPID) as MPNAME,
>PCTARGETRELEASEDATE,
>PCLISTPRICE,
>PCRRP,
>PCDAYONE,
>PCMINORDERQUANTITY
>from PRODUCTS join ORDERITEMSTABS_MP on PCMPID=OMMPID
>where OMOAID=3
>and PCACTIVE=1
>
>or 2)
>select PCID,
>PCPAID,
>PCMPID,
>PCNR,
>PCNAME1,
>(select PANAME from PRODUCTAREAS where
> PAID=PRODUCTS.PCPAID) as PANAME,
>(select MPNAME from MAINPRODUCTGROUPS where
> MPID=PRODUCTS.PCMPID) as MPNAME,
>PCTARGETRELEASEDATE,
>PCLISTPRICE,
>PCRRP,
>PCDAYONE,
>PCMINORDERQUANTITY
>from PRODUCTS
>where PCMPID in (select OMMPID from ORDERITEMSTABS_MP where OMOAID=3)
>and PCACTIVE=1
>
>I prefer the first one, because it's faster.

There is a third query that is faster than both:

select pr.PCID,
pr.PCPAID,
pr.PCMPID,
pr.PCNR,
pr.PCNAME1,
(select pa.PANAME from PRODUCTAREAS pa where
pa.PAID=pr.PCPAID) as PANAME,
(select mpg.MPNAME from MAINPRODUCTGROUPS mpg where
mpg.MPID=pr.PCMPID) as MPNAME,
pr.PCTARGETRELEASEDATE,
pr.PCLISTPRICE,
pr.PCRRP,
pr.PCDAYONE,
pr.PCMINORDERQUANTITY
from PRODUCTS pr
where
pr.PCACTIVE=1
and exists (select 1 from ORDERITEMSTABS_MP oit
where oit.OMMPID = pr.PCMPID
and oit.OMOAID=3)

However, this is NOT going to cure your problem, since you STILL have a
WHERE clause in your lookup query. Make this query into a view.

Helen