Results 1 to 2 of 2

Thread: Join Problem

  1. #1
    Join Date
    Nov 2011
    Posts
    3

    Unanswered: Join Problem

    I have two tables:

    Products

    ItemID ItemName Packing Price
    1 abc 10's 110
    2 xyz 12's 212
    3 alpha 10's 220
    4 beta 5's 300
    5 gamma 1's 135
    6 bravo 1's 167
    7 charlie 1's 120
    8 Mewa 1's 219



    Sales

    UserID SID SDate ItemID SaleQty
    101 1 2012-01-01 1 10
    101 1 2012-01-01 3 12
    101 1 2012-01-01 4 13
    110 2 2012-01-03 3 25
    110 2 2012-01-03 4 23
    110 2 2012-01-03 5 28
    101 3 2012-01-05 1 10
    101 3 2012-01-05 2 19
    101 3 2012-01-05 3 23
    101 3 2012-01-05 4 29

    The Required ResultSet is as following:

    ItemID ItemName Packing Price SaleQTY
    1 abc 10's 110 10
    2 xyz 12's 212 0
    3 alpha 10's 220 12
    4 beta 5's 300 13
    5 gamma 1's 135 0
    6 bravo 1's 167 0
    7 charlie 1's 120 0
    8 Mewa 1's 219 0


    I have tried with Left Join but no luck...Please Guide that how to this resultset which should based on condition
    that Sales.sDate='2012-01-01' and Sales.UserID=101 and all the rows from Products Table Should be reterive either the saleQty is available or not in Sales Table.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT p.ItemID 
         , p.ItemName 
         , p.Packing 
         , p.Price
         , COALESCE(SUM(s.SaleQty,0) AS SaleQty
      FROM Products AS p
     LEFT OUTER
      JOIN Sales AS s
        ON s.ItemID = p.ItemID
       AND s.sDate = '2012-01-01' 
       AND s.UserID = 101 
    GROUP
        BY p.ItemID
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •