Working with two tables - total from one via ID

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Working with two tables - total from one via ID

Author

Topic

richardlaw
Yak Posting Veteran

68 Posts

Posted - 2012-07-19 : 15:48:02

Hi

I have two tables, the first contains the information about a stock item (full details), the second is a list of items and their status.

What I would like is a table that shows the item name (taken from the first table), and then the total stock for that item (obtained via the second table - select all items where ID is from the first table).

I've tried the following, but it doesn't work.


SELECT     dbo.tbl_StockControl.Stock_Title, dbo.tbl_StockControl.Stock_DisplayInShop, dbo.tbl_StockControl.Stock_SellPrice, dbo.tbl_StockControl.Stock_UsedItem, 
                      dbo.tbl_StockControl.Stock_Cost,
                          (SELECT SUM  dbo.tbl_StockControl_GroupItems.Stock_GroupItem_Quantity WHERE Stock_GroupItem_StockID = dbo.tbl_StockControl.StockID) AS Quantity
FROM         dbo.tbl_StockControl CROSS JOIN
                      dbo.tbl_StockControl_GroupItems

Any support would be much appreciated.

Thanks in advance!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-19 : 16:07:00

you should be using inner join.


SELECT sc.Stock_Title, 
sc.Stock_DisplayInShop, 
sc.Stock_SellPrice, 
sc.Stock_UsedItem, 
sc.Stock_Cost,
sg.TotalQty
FROM dbo.tbl_StockControl sc
INNER JOIN (SELECT Stock_GroupItem_StockID,SUM(Stock_GroupItem_Quantity) AS TotalQty
            FROM dbo.tbl_StockControl_GroupItems
            GROUP BY Stock_GroupItem_StockID)sg
ON sg.Stock_GroupItem_StockID = sc.Stock_GroupItem_StockID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

richardlaw
Yak Posting Veteran

68 Posts

Posted - 2012-07-19 : 16:08:31

Fantastic. Thank you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-19 : 22:36:10

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources