SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Working with two tables - total from one via ID		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

richardlaw
Yak Posting Veteran

United Kingdom
63 Posts
Posted - 07/19/2012 :  15:48:02  Show Profile  Visit richardlaw's Homepage  Reply with Quote
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

India
47189 Posts
Posted - 07/19/2012 :  16:07:00  Show Profile  Reply with Quote
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/
Go to Top of Page

richardlaw
Yak Posting Veteran

United Kingdom
63 Posts
Posted - 07/19/2012 :  16:08:31  Show Profile  Visit richardlaw's Homepage  Reply with Quote
Fantastic. Thank you.
Edited by - richardlaw on 07/19/2012 16:08:45
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47189 Posts
Posted - 07/19/2012 :  22:36:10  Show Profile  Reply with Quote
wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000