Check Sums!

• mark richardson

  SSC Eights!

  Points: 807

  More actions

  January 11, 2005 at 7:18 am

  #87775

  Hi

  I would like a sql statement that does the following:

  I have 2 x tables (order & items)

  The order table contains line items including 'invoice_no' and 'val'

  The items table links to the order table with matching 'invoice_no', and contains a TOTAL_VAL

  I would like to write sql code that returns:

  get EVERYTHING from the order table where sum(val) <>

  item.TOTAL_VAL and the order.invoice_no = item.invoice_no

  I hope this makes sense.

  Basically, these values should always match but a user has just found 1 that doesn't and i'd like to see if there are any others.

   

  Thanks in advance,

   

  Mark

• Adam Machanic

  SSCoach

  Points: 15370

  More actions

  January 11, 2005 at 7:50 am

  #536812

  Why are you storing this data twice? Store it *once* (in the orders) and create a view that aggregates it.

  Anyway, here's the query you asked for:

  SELECT Item.*

  FROM Item

  WHERE TOTAL_VAL

  (SELECT sum(val)

  FROM Order

  WHERE Order.invoice_no = item.invoice_no)

  --
  Adam Machanic
  whoisactive