By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 1,183 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

Complex query with different conditions...

P: n/a
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

Jul 17 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
ja********@gmail.com wrote:
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
CD********@FortuneJames.com

Jul 17 '06 #2

P: n/a
CD********@FortuneJames.com wrote:
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);
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
CD********@FortuneJames.com

Jul 17 '06 #3

P: n/a
DFS
ja********@gmail.com wrote:
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)
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

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

Jul 17 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.