jason.teen@gmail.com wrote:
Quote:
Hi,
>
I have a table such as below and I want to write a query which says
"For each product, Exclude all Rows where Rate is 0 and Code is not D,
but if any of the rows for that product is found with an Indicator is
L, then only use that row and ignore the rest (just for that product)
>
I have tried some way, but never get the exact output I want and the
nested joins I think 3 or 4 in total, make it impossibly slow to run...
Any ideas??
>
Thanks..
>
>
>
tblOriginal
-------------------------------------------------------------------------
PRODUCT CODE RATE INDICTOR
-------------------------------------------------------------------------
One 1 23.00 P
One Z 0 P
One D 0 W
One 1 23.00 S
One Z 0 P
One D 0 W
Two 1 23.00 L
Two Z 0 W
Two D 0 W
Two 1 23.00 S
>
>
tblOutput
-------------------------------------------------------------------------
PRODUCT CODE RATE INDICTOR
-------------------------------------------------------------------------
One 1 23.00 P
One D 0 W
One 1 23.00 S
One D 0 W
Two 1 23.00 L
See if the following is adequate. Note: I added a primary key OID to
tblOriginal:
SELECT OID, PRODUCT, CODE, RATE, INDICATOR FROM tblOriginal WHERE
(CODE="D" OR RATE<>0) AND NOT EXISTS (SELECT A.PRODUCT FROM tblOriginal
AS A WHERE A.PRODUCT = tblOriginal.PRODUCT AND A.INDICATOR="L" GROUP BY
A.PRODUCT) UNION SELECT OID, PRODUCT, CODE, RATE, INDICATOR FROM
tblOriginal WHERE ((CODE="D" OR RATE<>0) AND INDICATOR = "L") AND NOT
EXISTS (SELECT A.OID FROM tblOriginal AS A WHERE A.PRODUCT =
tblOriginal.PRODUCT AND A.INDICATOR = "L" AND A.OID < tblOriginal.OID);
The first piece adds records from products where there is not an "L"
indicator. The final NOT EXISTS is to eliminate any "L" indicator
records for a single product beyond the first "L." I only tried it
with your data plus your data with an extra "L" indicator for Product
Two. I decided on using the primary key instead of using something
like UNION SELECT ALL because of that.
James A. Fortune
CDMAPoster@FortuneJames.com 
