How to join two tables - SQL Server Forums

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

How to join two tables

Author

Topic

deanfp
Starting Member

26 Posts

Posted - 2012-07-16 : 06:25:48

Hi

I have two SQL Scripts that I run to track sales of our products. The first below shows us the product SKU, quantity and amount sold. The second shows what Store ID each SKU is mapped to. How can I join both so that the SKU's StoreID is shown in the first Script?

Script 1

USE [multistorenc]
GO
select os.OrderedProductSKU, convert(DECIMAL(15,2),SUM(os.OrderedProductPrice)) totalsales,
cast(os.OrderedProductName as nvarchar(255)) Name, SUM(os.Quantity) Qty
from Orders_ShoppingCart os
join Orders o on o.OrderNumber = os.OrderNumber
where o.OrderDate between '2011-04-01 00:00:00.000' and '2012-04-01 00:00:00.000'
group by os.OrderedProductSKU, cast(os.OrderedProductName as nvarchar(255))
order by 2 desc

Script 2

select p.SKU, p.Name, ps.StoreID
from dbo.Product p with (NOLOCK)
join dbo.ProductStore ps with (NOLOCK) on ps.ProductID = p.ProductID
order by p.SKU, ps.StoreID

Thanks

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-16 : 06:45:27

select ps.StoreID, os.OrderedProductSKU, convert(DECIMAL(15,2),SUM(os.OrderedProductPrice)) totalsales,
cast(os.OrderedProductName as nvarchar(255)) Name, SUM(os.Quantity) Qty
from Orders_ShoppingCart os
join Orders o on o.OrderNumber = os.OrderNumber
join Product p on os.OrderedProductSKU = p.SKU
join dbo.ProductStore ps on ps.ProductID = p.ProductID
where o.OrderDate between '2011-04-01 00:00:00.000' and '2012-04-01 00:00:00.000'
group by os.OrderedProductSKU, cast(os.OrderedProductName as nvarchar(255))
order by 2 desc

Are products unique by store?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

deanfp
Starting Member

26 Posts

Posted - 2012-07-16 : 07:02:31

Hi

Yes they are assigned to one store. I tried to execute the script but got this

Column 'dbo.ProductStore.StoreID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-16 : 07:44:47

select StoreID = max(ps.StoreID), os.OrderedProductSKU, convert(DECIMAL(15,2),SUM(os.OrderedProductPrice)) totalsales,
cast(os.OrderedProductName as nvarchar(255)) Name, SUM(os.Quantity) Qty
from Orders_ShoppingCart os
join Orders o on o.OrderNumber = os.OrderNumber
join Product p on os.OrderedProductSKU = p.SKU
join dbo.ProductStore ps on ps.ProductID = p.ProductID
where o.OrderDate between '2011-04-01 00:00:00.000' and '2012-04-01 00:00:00.000'
group by os.OrderedProductSKU, cast(os.OrderedProductName as nvarchar(255))
order by 2 desc

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

deanfp
Starting Member

26 Posts

Posted - 2012-07-16 : 07:48:51

Perfect! THanks! I can see now how this is done.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources