sql query help

• srathna77

  SSCommitted

  Points: 1982

  More actions

  January 5, 2009 at 4:34 pm

  #129727

  There are few products in my table all products will have levels

  Productid product name level

  1 abc cfa

  2 cdf cpa

  3 sd ppp

  4 dd perl

  5 ff Cfah

  6 fg Cfag

  7 hj Cfat

  8 kl Cfay

  9 mn Cfar

  10 op Cfaf

  11 qr Cfa4

  If any body ordered more than two productid of same level is fraud and different level also fraud but

  If any body ordered cfa and cpa together it is not included

  And

  Perl and any product it is also not included

  And cfar and cfa4 not included.

  I need to write query to produce the report plz help

  Help Appreciated.

  Thanks in Advance.

• David Betteridge

  Hall of Fame

  Points: 3264

  More actions

  January 5, 2009 at 4:55 pm

  #921502

  Not sure that I fully understand your question, but this might help

  select *

  from Products P

  join Products P2 on P.ProductName = P2.ProductName

  and P.Level = P2.Level

  and P.ProductID != P2.ProductID

  where P.ProductName != 'Perl'

  and (not (P.ProductName = 'cfar' and P2.ProductName = 'cfa4'))

  and (not (P.ProductName = 'cfa4' and P2.ProductName = 'cfar'))

  and (not (P.ProductName = 'cfa' and P2.ProductName = 'cpa'))

  and (not (P.ProductName = 'cpa' and P2.ProductName = 'cfa'))