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,
(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
Any support would be much appreciated.
Thanks in advance!