How to list posts from one table and at the same time sum an amount from another table?

First of all, hope my English isn't too bad..

I have a problem I can't solve. The thing is that I am thinking about an order handling system.. What I have so far is a table where every article registrats that belong to an order like this

Tbl_orderLines id, prod_week, article_id

Then i also have och table where the amount for each row in tbl_orde开发者_JAVA技巧rLines registrates like this

Tbl_inventory orderline_id, article_id, amount

Now article_id in both tables are the same (double post) thought it would be easier to do so? What I want to is the get the available amount of each specific article at a specific time. So I want to be able to list every row in Tbl_orderLInes and with a join get the amount for every row, but! I also want to get the total amount of a specific article. Therefore, I want to sum the amount column from Tbl_inventory where the article_id is the same as the row from tbl_orderLines.

This is what I've got now Tbl_orderLines

id         prod_week        article_id    
 1           1130              2   
 2           1129              5          
 3           1129              2     
 4           1128              2   

Tbl_inventory

orderLine_id         article_id      amount     
 1                       2               1          
 2                       5               2          
 3                       2               1          
 4                       2               2          

The result should be something like

id         prod_week        article_id      amount     TotalAmount
 1           1130              2               1          4
 2           1129              5               2          2
 3           1129              2               1          3
 4           1128              2               2          2

Don't know if its possible to understand my question? I'm using MSSQL..

Much appreciate any help..

I believe the first part of your result set is achieve by the following.

SELECT
  [order_line].id,
  [order_line].prod_week,
  [order_line].article_id,
  [inventory].amount
FROM
  Tbl_orderLines             AS [order_line]
INNER JOIN
  Tbl_inventory              AS [inventory]
    ON [inventory].orderline_id = [order_line].id

I'm not 100% certain about the TotalAmount column, however. My best guess is that you want...The sum of the amounts for all preceeding weeks with the same article_id?

SELECT
  [order_line].id,
  [order_line].prod_week,
  [order_line].article_id,
  [inventory].amount,
  SUM([historic_inventory].amount) AS TotalAmount
FROM
  Tbl_orderLines             AS [order_line]
INNER JOIN
  Tbl_inventory              AS [inventory]
    ON [inventory].orderline_id = [order_line].id
INNER JOIN
  Tbl_orderLines             AS [historic_orders]
    ON [historic_orders].article_id = [order_line].article_id
    AND[historic_orders].prod_week <= [order_line].prod_week
INNER JOIN
  Tbl_inventory              AS [historic_inventory]
    ON [historic_inventory].orderline_id = [historic_order_line].id
GROUP BY
  [order_line].id,
  [order_line].prod_week,
  [order_line].article_id,
  [inventory].amount

I can't get exactly what you want but I believe you can try something like this enhance the idea towards your goal.

CREATE TABLE [dbo].[inventory](
    [id] [int] NULL,
    [article_id] [int] NULL,
    [amount] [int] NULL
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[orderLines](
    [id] [int] NULL,
    [prod_week] [int] NULL,
    [article_id] [int] NULL
) ON [PRIMARY]

GO

SELECT ol.id, 
       ol.prod_week, 
       ol.article_id, 
       i.amount, 
       SUM(amount) OVER (PARTITION BY i.article_id) AS totalAmount
  FROM dbo.orderLines AS ol, dbo.inventory AS i
 WHERE ol.id = i.id
 ORDER BY ol.id