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

  Productidproduct name level

  1abccfa

  2cdfcpa

  3sdppp

  4ddperl

  5ffCfah

  6fgCfag

  7hjCfat

  8klCfay

  9mnCfar

  10opCfaf

  11qrCfa4

  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'))