Hi, I have two tables: Product.t

[4181944]

Hi,
I have two tables:
Product.tbl
-------------------------
ProductID | ProductName |
------------|--------------
1 | CD-ROM |
----------|--------------
2 | RAM |
----------|--------------
3 | Monitor |
-------------------------
Category.tbl
-------------------------
ProductID | Category |
----------|--------------
1 | 52X |
----------|--------------
1 | 36X |
----------|--------------
1 | 24X |
----------|--------------
2 | 512MB |
----------|--------------
2 | 256MB |
----------|--------------
1 | 48X |
----------|--------------
3 | 19" |
----------|--------------
3 | 17" |
----------|--------------

Can someone tell me how to write a query that can generate the below table:
-------------------------------------------
ProductID | ProductName | Num-of-Category |
----------|-------------|------------------
1 | CD-ROM | 4 |
----------|-------------|--------------------
2 | RAM | 2 |
----------|-------------|-------------------
3 | Monitor | 2 |
-------------------------------------------
My query is:

Select *, (Select count(*) from Category where ProductID=1) as Num-of-Category
from Product

this will generate like:
-------------------------------------------
ProductID | ProductName | Num-of-Category |
----------|--------------------------------
1 | CD-ROM | 4 |
----------|--------------------------------
2 | RAM | 4 |
----------|--------------------------------
3 | Monitor | 4 |
-------------------------------------------

which is uncorrect for row2 and row 3. Please help to write this query.

Many thanks

Richard

 

[LFCfan]

Select
*,
(Select count(*) from Category
where ProductID = Product.ProductID) as Num-of-Category
from Product

not tested, but should work

hope this helps
LFCfan