Complex query with different conditions...

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

jason.teen@gmail.com wrote: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

CDMAPoster@FortuneJames.com wrote:I think the GROUP BY A.PRODUCT is redundant since only one product can
satisfy the condition so try it without that.

James A. Fortune
CDMAPoster@FortuneJames.com

jason.teen@gmail.com wrote:The 2nd row of your output table below violates the conditions of this
request.

You may need to rephrase your query (did you mean to say Exclude? Why don't
you think in terms of Include?), or provide more test data and the PK of the
table. Can every product have any code (1,Z,D), any rate, and any indicator
(L,P,S,W)? It sounds like each product can have only one L indicator?

I think this query does what you want, but I'm not sure I understand the
question, and more info. would be helpful.


SELECT *
FROM Table1
WHERE INDICATOR = 'L'
UNION
SELECT *
FROM Table1
WHERE CODE = 'D'
AND RATE 0
AND INDICATOR <'L'
AND PRODUCT NOT IN
(SELECT DISTINCT PRODUCT
FROM TABLE1
WHERE INDICATOR = 'L'
);


The result is one row:

PRODUCT CODE RATE INDICATOR
Two 1 $23.00 L