CODEWITHSUNDEEP
sql-serversql-server-2008sql-server-2008-r2sql-server-2012
CodeNinja
CodeNinja

Reputation: 3278

Query using a set of values

I have database tables for 

Stores
StoreId, Name

Products
ProductId, Name

Transactions 
TransactionId, StoreId, ProductId

I was just given an excel file with a list of 300 Stores.

I need to find out if these stores are selling our products and if they are , how many products they are selling.

One way of doing this , that I can think of right now is individually querying the Transactions table for each of the store in the excel sheet and then copy the results output back to the excel sheet.

Is there a way I can write a query against all the Store names from the excel file ? I need to get this done in the next few hours. Please let me know if there are easier approaches to this

Thanks !!

Upvotes: 0

Views: 28

Answers (1)

BAReese
BAReese

Reputation: 501

This query will do it:

select     Stores.StoreId, Stores.Name,
           Products.ProductId, Products.Name,
           count(distinct TransactionId) as Sales
from       Transactions
inner join Stores on Stores.StoreId = Transactions.StoreId
inner join Products on Products.ProductId = Transactions.ProductId
where      Stores.StoreId in
(
 1
,2
,3
,4
)
group by   Stores.StoreId, Stores.Name,
           Products.ProductId, Products.Name

In order to fill out your list of ScoreId's for the Where clause, concatenate a comma to the front or end of each item in your list in Excel. Such as below:

="," & A2

then autofill down. That new piece can be copy/pasted into SQL Server Management Studio.

Upvotes: 1

Related Questions