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

.

Relevant Pages

  • Re: How to combine existing collections to include or exclude...
    ... Here is a working query: ... machines to exclude, the primary collection would be modified as well. ... Servers you want minus the ones you don't want ... Add computers you don't want to show up in Collection B ...
    (microsoft.public.sms.admin)
  • RE: filter for values in another table
    ... But you indicated that you wanted to exclude all of the values where the ID ... SELECT TableA.* ... Would I just enter all of the ids into the parenthesis? ... runs is to create a query that some people call a "frustrated outer join". ...
    (microsoft.public.access.queries)
  • Re: Help with counting only one time
    ... You want to exclude all but the first row for each order? ... In the following example, I will assume there is a primary key field named ID, and the table is called Table1. ... but I am clueless on what to do in the query even ...
    (microsoft.public.access.queries)
  • CONTAINSTABLE - weird results - using "and not"
    ... working on an upgrade to my search page to allow users to exclude ... Select FT_TBL.UID as ID, FID, Category, Link, target, Title, SubTitle, ... Description, LastUpdate, LU_SearchCategories.TypeName, ... The top results in the query above returns a record that also contain ...
    (microsoft.public.sqlserver.fulltext)
  • Excluding data from a query
    ... query that excludes certain criteria using a Between and clause but I ... need to exclude more cirteria from that query and I can't seem to get ... This database tracks advertising on buses by side. ...
    (microsoft.public.access.queries)