CODEWITHSUNDEEP
sqlsql-serverjoinstored-procedures
Liam neesan
Liam neesan

Reputation: 2551

How to make join in my query scenario in MS SQL Server?

I have to 2 tables called 1.#records and 2.ItemRelation

1.#records

ID      ItemLookupCode    Qty   ParentItem  StoreID
---------------------------------------------------
70707   130679C0400       -1    0            1004    --parent
78910   130679T0020       19    70707        1004    --child1
70706   130679T0010        1    78910        1004    --child2
45913   130679P0001        9    70706        1004    --child3

70707   130679C0400        1    0            1001    --Parent
78910   130679T0020        0    70707        1001    --child1
70706   130679T0010        0    78910        1001    --child2
                                                     --no child3

2.ItemRelation

 ID   ItemLookupCode  ChildID1  ChildParentItem1 ChildParentQty1 ChildID2 ChildParentQty2 ChildID3 ChildParentQty3 LastChildParentQty
70707 130679C0400     78910     70707            20               70706   2               45913           10                  400

Expecting Result

Expecting result same like an ItemRelation table. But add the Qty and storeId on respective child

UPDATE

ItemRelation = ir
#records = rec

rec.StoreID | ir.ID | rec.Qty as ParentQty | ir.ChildID1 | rec.Qty as ChildQty1 | ir.ChildID2 | rec.Qty as ChildQty2 | ir.ChildID3 | rec.Qty as ChildQty3 | ir.lastchildQty
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1004          70707    -1                     78910          19                      70706              1                  45913            9                 400
1001          70707     1                     78910          0                       70706              0                                                     400

Upvotes: 0

Views: 57

Answers (3)

Liam neesan
Liam neesan

Reputation: 2551

Finally I got my result

SELECT RecParent.StoreID,
   ir.ID,
   RecParent.Qty as ParentQty,
   ir.ChildID1,
   RecChild1.Qty as ChildQty1,
   ir.ChildID2,
   RecChild2.Qty as ChildQty2,
   ir.ChildID3 ,
   RecChild3.Qty as ChildQty3,
   ir.LastChildParentQty
FROM ItemRelation Ir 
    INNER JOIN #records RecParent ON Ir.ID  = RecParent.ID and RecParent.ParentItem = 0
    Left JOIN #records RecChild1 ON Ir.ChildID1  = RecChild1.ID and ir.ID = RecChild1.ParentItem and RecParent.StoreID = RecChild1.StoreID
    Left JOIN #records RecChild2 ON Ir.ChildID2  = RecChild2.ID and ir.ChildID1 = RecChild2.ParentItem and RecParent.StoreID = RecChild2.StoreID
    Left JOIN #records RecChild3 ON Ir.ChildID3  = RecChild3.ID and ir.ChildID2 = RecChild3.ParentItem and RecParent.StoreID = RecChild3.StoreID

It's bringing exact result.

Thank you guyz

Upvotes: 0

IngoB
IngoB

Reputation: 2969

select parentRec.StoreID, ir.ID, parentRec.Qty as ParentQty,
       childRec1.ID as ChildID1, childRec1.Qty as ChildQty1, 
       childRec2.ID as ChildID2, childRec2.Qty as ChildQty2, 
       childRec3.ID as ChildID3, childRec3.Qty as ChildQty3
  from ItemRelation ir
  join #records parentRec on parentRec.Id = ir.id
  join #records childRec1 on childRec1.ParentItem = parentRec.ID
  join #records childRec2 on childRec2.ParentItem = childRec1.ID
  join #records childRec3 on childRec3.ParentItem = childRec2.ID

Upvotes: 1

Ilyes
Ilyes

Reputation: 14928

Using INNER JOIN :

SELECT rec.StoreID,
       ir.ID,
       rec.Qty as ParentQty,
       ir.ChildID1,
       rec.Qty as ChildQty1,
       ir.ChildID2,
       rec.Qty as ChildQty2,
       ir.ChildID3 ,
       rec.Qty as ChildQty3,
       ir.lastchildQty
FROM ItemRelation Ir INNER JOIN #records Rec
ON Ir.ID  = Rec.ID;

Upvotes: 1

Related Questions