Counting rows part II

[chribben]

Hi,
I have the following query:
1. SELECT products.name, COUNT(reviews.r eview)
2. FROM (select distinct name from products) products
3. FULL JOIN reviews ON products.name = reviews.product name
4. GROUP BY products.name
which lists product names and the number of reviews for each product. I need to include one more column from the products table in the table resulting from the above query. This column (called altname) can have different values for a certain product (each product can appear multiple times in the table). It doesn't matter which of the altname values for a product that is presented in the resulting table as long as someone is. Is this possible? See example below.
The products table:
products.name products.altnam e
ProdA prd_a
ProdA proda
ProdB prb
ProdB prodb

...and the review table:
reviews.prodnam e review
ProdA bla bla....
ProdA bla bla....
ProdB bla bla....

should result in:
name altname reviews_count
ProdA prd_a 2
ProdB prb 1

/Chris

[debasisdas]

Is it such that for the same products.name you have different products.altnam e ?

[chribben]

Is it such that for the same products.name you have different products.altnam e ?

Yes, that is correct.

/Chris

[ck9663]

You might want to reconsider re-designing your tables and their relationship.

-- CK