Hi,

> > Create for example a VIEW V_SYS_IDLISTS with :
> >
> > SELECT DISTINCT
> > GROUP_ID,
> > ID
> > FROM
> > SYS_IDLISTS
> >
> > and use this view in a join
> >
> > SELECT
> > pres.productid
> > FROM
> > presence pres
> > join product prod on (pres.productid = prod.id)
> > join V_SYS_IDLISTS v on (v.ID = pres.outletid)
> > where
> > (pres.valuedate >= '2003-09-01') and (pres.valuedate < '2003-11-01')
> >
>
> But this won't give me the same resultset. You set the distinct on
> SYS_IDLISTS, I need it on "pres.productid".
>
> Any other hint ?

The view was to remove the IN predicate what should speed up this query. I
hadn't understood that there were also duplicates for productid and that you
also want a distinct in de select. Then just try these 2 options :

1)
SELECT
DISTINCT
pres.productid
FROM
presence pres
JOIN product prod ON (pres.productid = prod.id)
JOIN V_SYS_IDLISTS v ON (v.ID = pres.outletid)
WHERE
(pres.valuedate >= '2003-09-01') and (pres.valuedate < '2003-11-01')

2)
SELECT
pres.productid
FROM
presence pres
JOIN product prod ON (pres.productid = prod.id)
JOIN V_SYS_IDLISTS v ON (v.ID = pres.outletid)
WHERE
(pres.valuedate >= '2003-09-01') and (pres.valuedate < '2003-11-01')

GROUP BY
pres.productid


Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Firebird open source database (based on IB-OE) with many SQL-99 features :
http://www.firebirdsql.org
http://www.firebirdsql.info
http://www.fingerbird.de/
http://www.comunidade-firebird.org/

Support list for Interbase and Firebird users :
firebird-support@yahoogroups.com

Nederlandse firebird nieuwsgroep :
news://80.126.130.81