hello,
I have the query below. To retrieve the number of merchants in the merchants table and the number of related products each merchant has.

Now when i run this query, i get the merchants in table a
2. the total number of products repeated in table b. (THE SAME FOR ALL MERCHANTS)

How do i get each distinct merchant products assigned to his name ?

something like this

MERCHANT PRODUCT

Afrika 45
Sony 23
Nokia 59

etc

thanks
Afrika

Code:
SELECT     a.merchants, b.products
FROM         (SELECT DISTINCT (merchant) merchants
                       FROM          dbo.markets_product_registration) a CROSS JOIN
                          (SELECT     COUNT(products) AS products
                            FROM          dbo.markets_products
                            WHERE      (status = 1)) b