I have 2 tables, Shops and Products. For each shop, there are several products that are assigned to them. Meaning, WalMart has 4 types of products and K-Mart has 2.


-------------------
 ShopID | ShopName
-------------------
    A | WalMart
    B | K-Mart
-------------------

--------------------------------
 ProductID | Name | ShopID
--------------------------------
    1 | Apple | A
    2 | Banana | A
    3 | Mango | A
    4 | Grapes | A
    5 | Orange | B
    6 | Lemon | B
--------------------------------


I want to make a select statement that will come up with the following result:


-------------------------
  ShopName | Quantity
-------------------------
  WalMart | 4
  K-Mart | 2
-------------------------


... where for each shop, the number of products assigned to the shop is counted and placed under the Quantity column. And please note that ShopNames can go as up to, let's say, 5000 shops. So--- CASE or IF-ELSE statements won't do.

Please help! Thanks!

Sample Table Structure and Data

Create table ShopMaster
(
ShopId char(1),
ShopName varchar(25)
)
Go
Insert into ShopMaster values ('A','WalMart')
Insert into ShopMaster values ('B','K-Mart')
Go

Create table ProductInfo
(
ProductId int,
ProductName varchar(25),
ShopId char(1)
)
Go

Insert into ProductInfo values (1,'Apple','A')
Insert into ProductInfo values (2,'Banana','A')
Insert into ProductInfo values (3,'Mango','A')

Insert into ProductInfo values (4,'Grapes','A')
Insert into ProductInfo values (5,'Orange','B')
Insert into ProductInfo values (6,'Lemon','B')
Go

Query which would fetch you the expected result

Select ShopName, Quantity=Count(P.ProductName)
From ShopMaster S, ProductInfo P
Where S.ShopId = P.ShopId
Group by ShopName

Best Regards
Vadivel

MVP ASP/ASP.NET
http://vadivel.thinkingms.com