query problem

• znkin

  Old Hand

  Points: 382

  More actions

  January 3, 2006 at 6:39 am

  #91153

  I have 2 tables ordermaster and orderdetails with the following design

  ordermaster has the columns

  PK_OrderId ParentOrderid Containername

  100             0                abc

  101             100              abc

  102              100             abc

  orderdetails has the columns

  FK_OrderId  OrderAmount   OrderType

  100                 1000             T

  101                  700              T

  102                  300               T

  The 2 tables have a one to many relationship with

  PK_OrderId being a primary key in ordermaster and FK_OrderId being a foreign key in orderdetails.

  The orderids 101 & 102 are child records to orderid 100 by the column ParentOrderid

  What output I want is a list of all unique OrderIds whose OrderAmount is greater than the sum of OrderAmount of its child orderids.

  Any help

  Thanks a lot.

   

   

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736478

  More actions

  January 3, 2006 at 8:07 am

  #612399

  select a.orderid

  from ordermaster a

  inner join orderdetails b

  on a.pk_orderid = b.fk_orderid

  where b.orderamount (select sum( c.orderamount)

  from orderdetails c

  inner join ordermaster d

  on c.fk_orderid = d.pk_orderid

  and d.parentorderid = a.pk_orderid)

  I think