Compare Parent and Child Data

• ningaraju.ne 46825

  Old Hand

  Points: 353

  More actions

  August 4, 2015 at 5:11 am

  #302973

  DECLARE @ParentTable TABLE (ProductID BIGINT,ItemID BIGINT)

  DECLARE @ChildParentTable TABLE (ParentID BIGINT,ProductID BIGINT,ItemID BIGINT)

  --In Parent table(@ParentTable) there are 4 and 5 items each for product 101 and 102 respectively

  INSERT INTO @ParentTable(ProductID,ItemID) VALUES(101,1234),(101,1578),(101,1590),(101,1237)

  INSERT INTO @ParentTable(ProductID,ItemID) VALUES(102,5465),(102,5466),(102,5474),(102,5489),(102,6543)

  --child products 701 and 901 are derived from parent products(partially) 101 and 102 respectively

  INSERT INTO @ChildParentTable(ParentID,ProductID,ItemID) VALUES(101,701,1234),(101,701,1590),(101,701,1578)

  INSERT INTO @ChildParentTable(ParentID,ProductID,ItemID) VALUES(901,102,5465),(901,102,5474),(901,102,8976)

  --Here what I need is

  --For product 701 there is one item missing ie 1237 which exists in its parent 101

  --and For product 901 there are 3 items missing ie 5466,5489 and 6543 which exists in its parent 102

  --and extra item exists ie 8976 so my result table should lokk like this

  /*

  ParentProductID ProductIDItemIDIsExtra IsMissing

  1017011237 01

  1029015466 01

  1029015489 01

  1029016543 01

  1029018976 10

  */

• Luis Cazares

  SSC Guru

  Points: 183698

  More actions

  August 4, 2015 at 8:13 am

  #1817120

  This can be a complicated issue and it's called relational division. You can search the web about it and you'll find lots of information to learn about it.

  Your sample data had an error on product 102, but this should do the trick.

  DECLARE @ParentTable TABLE (ProductID BIGINT,ItemID BIGINT);

  DECLARE @ChildParentTable TABLE (ParentID BIGINT,ProductID BIGINT,ItemID BIGINT);

  INSERT INTO @ParentTable(ProductID,ItemID) VALUES(101,1234),(101,1578),(101,1590),(101,1237);

  INSERT INTO @ParentTable(ProductID,ItemID) VALUES(102,5465),(102,5466),(102,5474),(102,5489),(102,6543);

  INSERT INTO @ChildParentTable(ParentID,ProductID,ItemID) VALUES(101,701,1234),(101,701,1590),(101,701,1578);

  INSERT INTO @ChildParentTable(ParentID,ProductID,ItemID) VALUES(102,901,5465),(102,901,5474),(102,901,8976);

  SELECT ISNULL( x.ParentProductID, c.ParentID) AS ParentProductID,

  ISNULL( x.ProductID, c.ProductID) AS ProductID,

  ISNULL( x.ItemID, c.ItemID) AS ProductID,

  CASE WHEN x.ParentProductID IS NULL THEN 1 ELSE 0 END AS IsExtra,

  CASE WHEN c.ParentID IS NULL THEN 1 ELSE 0 END AS IsMissing

  FROM @ChildParentTable c

  FULL

  JOIN (

  SELECT DISTINCT

  p.ProductID AS ParentProductID,

  c.ProductID AS ProductID,

  p.ItemID

  FROM @ChildParentTable c

  JOIN @ParentTable p ON p.ProductID = c.ParentID) x ON x.ParentProductID = c.ParentID

  AND x.ProductID = c.ProductID

  AND x.ItemID = c.ItemID

  WHERE x.ParentProductID IS NULL

  OR c.ParentID IS NULL

  ORDER BY ParentProductID;

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2