0

Possible Duplicate:
SQL Subquery , One to Multiple rows

Following query I have written returns the result as expected, example 121350 rows.

SELECT dbo.Articles_in_Consignment.Consignment_id,
  dbo.ORDER_D.Orders_boxsize,
  dbo.ORDER_D.Orders_boxweight,
  dbo.ORDER_D.Orders_boxlength,
  dbo.ORDER_D.Orders_boxwidth,
  dbo.ORDER_D.Orders_boxdepth,
  dbo.ORDER_D.Order_dispatchment_id,
  SUM(
    ISNULL(
       dbo.COMPONENT.Component_weight, 0) * 
       ISNULL(dbo.ORDER_ALLOCATE.Order_allocate_qty, 0)
  ) AS Component_weight,
  COUNT_BIG(*) AS _ic
FROM dbo.Articles_in_Consignment
   INNER JOIN dbo.ORDER_D
     ON  dbo.Articles_in_Consignment.Order_dispatchment_id = dboRDER_D.Order_dispatchment_id
   INNER JOIN dbo.ORDERS
     ON  dbo.ORDER_D.Order_dispatchment_id = dbo.ORDERS.Order_dispatchment_id
   INNER JOIN dbo.ORDER_ITEMS
     ON  dbo.ORDERS.Orders_id = dbo.ORDER_ITEMS.Orders_id
   INNER JOIN dbo.ORDER_ALLOCATE
     ON  dbo.ORDER_ITEMS.Order_items_id = dbo.ORDER_ALLOCATE.Order_items_id
   INNER JOIN dbo.STOCKIN
     ON  dbo.ORDER_ALLOCATE.Stockin_id = dbo.STOCKIN.Stockin_id
   INNER JOIN dbo.COMPONENT
    ON  dbo.STOCKIN.Component_id = dbo.COMPONENT.Component_id
WHERE  (dbo.ORDERS.Cold = 'No')
GROUP BY
  dbo.Articles_in_Consignment.Consignment_id,
  dbo.ORDER_D.Orders_boxsize,
  dbo.ORDER_D.Orders_boxweight,
  dbo.ORDER_D.Orders_boxlength,
  dbo.ORDER_D.Orders_boxwidth,
  dbo.ORDER_D.Orders_boxdepth,
  dbo.ORDER_D.Order_dispatchment_id 

However,WHEN i TRY TO ADD a subquery following SELECT statement the resulted ROWS increases TO 155550 row due TO subquery returning one TO many ROWS.

,(
  SELECT CASE 
    WHEN EXISTS (
      SELECT *
      FROM   dbo.ORDER_ITEMS
      WHERE  Component_id IN (1111, 1111)
             AND Orders_id = dbo.ORDERS.Orders_id
    ) THEN CAST(1 AS BIT)
    ELSE CAST(0 AS BIT)
    END
  ) AS DG

Ordering by OrderId.

How can I solve this so that my consolidation of first query does not break due to subquery?

Current result I am getting with subquery...

Consignment_id    Order_boxsize   Order_dispatchment_id   Component_weight   _ic   DG

--------------    -------------   --------------------    ----------------   ---  --

125                L                121212                    0.639           21   0
125                L                121212                    0.639           21   0

expected result with consolidation.

Consignment_id    Order_boxsize   Order_dispatchment_id   Component_weight   _ic   DG

--------------    -------------   --------------------    ----------------   ---  --

125                L                121212                    1. 278         42   0

https://stackoverflow.com/questions/14352512/sql-subquery-one-to-multiple-rows

Browse other questions tagged or ask your own question.