Re: Need some help with a SQL command

cmfuld wrote:
> I am relatively new to wriring code in SQL. I have written a query
> that evaluates Coupons used with Items sold in the same transaction..
> The query I have written works very well, but I would really like to
> skinny down the data that is returned.
>
> For Example, Cou A, was used to sell Item 1, Cou B was used to sell
> Item 1, and coupon A was used to sell Items 2.
>
> I need to figure out how to exclude certain records based on existence
> of multiple criteria. In this case, Cou B, Item 1 is a good
> transaction, and thus can be removed. Cou A Item 1 is suspect as well
> as Cou B Item 2.
>
> If I wrie the code like this, I think the following will happen
>
> Select *
> from Coupon c, Sales S
> where c.customer = s.Customer and
> c.Coupon <> A
> or S.Item <> 2
>
> this would return all 3 of the records
>
> Select *
> from Coupon c, Sales S
> where c.customer = s.Customer and
> c.Coupon <> A
> and S.Item <> 2
>
> This would not return any records,
>
> Select *
> from Coupon c, Sales S
> where c.customer = s.Customer and
> c.Coupon <> A
>
> Would return only one record,
>
> Select *
> from Coupon c, Sales S
> where c.customer = s.Customer
> and S.Item <> 2
>
> would only return one records as well.
>
> Can anybody point me in the right direction.
>
> There would be several other combinations of Coupon and Item that I
> would want to remove from the tabls
>
> Thanks
>
> Casey

After reading what you wrote 3 times I am still unsure what you are doing. Could you clarify and show some sample data?

My instinct is that NOT IN and NOT EXISTS might be what you are looking for but it is hard to tell: Perhaps MINUS and INTERSECT.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org