Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2012
    Posts
    3

    Unanswered: Stuck with this probably simple query

    Hello, I've got his query
    SELECT Stock.Stock_ID, Stock.Amount_Units FROM Stock
    Code:
    which outsputs likes this	// every item in the stock table, with an amount
    Stock_ID	Amount_Units
    1              2
    2		1
    3		1
    4		5
    5		1
    Etc
    
    and ive got this query..	// Items that have been sold, with an amount
    SELECT Items_Sold.Stock_ID, Items_Sold.Amount_Units
    
    Which outputs like this
    Stock_ID	Amount_Units
    4		4
    5		1
    
    
    How can i converge these two together, so i see how many of each Stock Item is left?
    
    Pseudo code --
    Foreach row in the Query 1, Match it too Query 2
    Then Foreach row that matches, DO (query1.Amount_Units - query2.Amount_Units)
    
    E.g
    
    Stock_ID	Amount_Units	Units_Left
    1		2			2
    2		1			1
    3		1			1
    4		5			1 // Affected
    5		1			0 // Affected
    Any help would be greatly appreciated!

    /Insidus

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT Stock.Stock_ID
         , Stock.Amount_Units 
         , COALESCE(Items_Sold.Amount_Units,0) AS Sold_Units
         , Stock.Amount_Units -  
           COALESCE(Items_Sold.Amount_Units,0) AS Units_Left
      FROM Stock
    LEFT OUTER
      JOIN Items_Sold
        ON Items_Sold.Stock_ID = Stock.Stock_ID
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2012
    Posts
    3
    Thanks mate, that's absolutely brilliant!
    There is however, one small problem, which i probably should have mentioned.

    In the Items_Sold Table, there may be two records for the same item.
    Code:
    Stock_ID       Amount_Units // Sold
    44                5
    44                2
    Is there a way to group them? So Stock_ID of 44 = Amount_Units 7,

    Thanks in Advanced!
    /Insidus

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Insidus View Post
    Is there a way to group them?
    Code:
    SELECT Stock.Stock_ID
         , Stock.Amount_Units 
         , COALESCE(SUM(Items_Sold.Amount_Units),0) AS Sold_Units
         , Stock.Amount_Units -  
           COALESCE(SUM(Items_Sold.Amount_Units),0) AS Units_Left
      FROM Stock
    LEFT OUTER
      JOIN Items_Sold
        ON Items_Sold.Stock_ID = Stock.Stock_ID
    GROUP
        BY Stock.Stock_ID
         , Stock.Amount_Units 
    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
  •