Find Sql parent rows with exactly the same set of child rows

Question

I have an MS SQL database with orders. There are two tables of orders and orders. I would like to receive all orders that have the same orders: this means showing orders that have exactly the same products and quantities.

create tables and data:

CREATE TABLE [dbo].[orders](
    [orderid] [int] NULL
) ON [PRIMARY]

GO

INSERT INTO orders values(1)
INSERT INTO orders values(2)
INSERT INTO orders values(3)
INSERT INTO orders values(4)

CREATE TABLE [dbo].[orderrows](
    [orderid] [int] NULL,
    [LineNum] [int] NULL,
    [Quantity] [decimal](18, 6) NULL,
    [ItemCode] [nvarchar](20) NULL
) ON [PRIMARY]

GO

INSERT INTO orderrows values(1,0,11.0,'Item1')
INSERT INTO orderrows values(1,1,12.0,'Item2')
INSERT INTO orderrows values(1,2,13.0,'Item3')
INSERT INTO orderrows values(1,3,14.0,'Item4')
INSERT INTO orderrows values(2,0,11.0,'Item1')
INSERT INTO orderrows values(2,1,12.0,'Item2')
INSERT INTO orderrows values(2,2,13.0,'Item3')
INSERT INTO orderrows values(2,3,14.0,'Item4')
INSERT INTO orderrows values(3,0,11.0,'Item1')
INSERT INTO orderrows values(3,1,12.0,'Item2')
INSERT INTO orderrows values(4,0,15.0,'Item5')
INSERT INTO orderrows values(4,1,16.0,'Item6')

I created this query:

select t1.orderid, t1.Itemcode, t1.quantity, t1.LineNum, t2.orderid, t2.Itemcode 
FROM orderrows t1
    LEFT OUTER JOIN orderrows t2 ON t1.itemcode = t2.ItemCode and t1.quantity = t2.Quantity and t2.LineNum = t1.linenum
    where t1.orderid <> t2.orderid
    order by t1.orderid, t2.orderid

This gives the following results:

Does anyone know how to get rid of crossed out lines? These orders are not an exact match, but only have a subset of orders. for example, lines 5 and 6 show order 3, but this order has only elements 1 and 2.

Answer 1

- orderids, EXCEPT . , LineNum "" .

-- Build up all combinations of orders
WITH orderPairs AS
(
    select o1.orderid as orderid1, o2.orderid as orderid2
    from orders o1
        cross join orders o2
    where 
        o1.orderid > o2.orderid -- prevent self matches, and duplicate checks
)
SELECT *
  FROM orderPairs op
  WHERE 
    NOT EXISTS
    -- All in O1 are in O2
    (SELECT ItemCode, Quantity 
      FROM orderrows orw1 
      WHERE orw1.orderid = op.orderid1 

      EXCEPT 

      SELECT ItemCode, Quantity 
      FROM orderrows orw2 
      WHERE orw2.orderid = op.orderid2)

    AND NOT EXISTS

    -- All in O2 are in O1
    (SELECT ItemCode, Quantity 
     FROM orderrows orw2 
      WHERE orw2.orderid = op.orderid2

      EXCEPT 

      SELECT ItemCode, Quantity 
      FROM orderrows orw1 
      WHERE orw1.orderid = op.orderid1 )

(, )

SqlFiddle , , Order1 Order2 .