I have a table containing two fields, 'user' and 'product'. Every 'user' can have several 'product' entries, and every 'product' can have several 'user' entries. This is basically the linking table in an m<->m relationship between the 'user' and the 'product' tables.
Now, I have a list 'users' and I want to retrieve the following information: an order list of 'products' which are the most popular among those 'users'. I only need the number of users for a given product, and I need the product ordered by the number of users.
So, if my user list is (1,2,3,4) and my table is something like
------------
1 a
2 b
3 b
4 c
2 a
4 a
5 c
6 c
7 cthen I would like to get
--------------
a 3
b 2
c 1
I know who I can do that by a simple select statement ordering on the 'products', and some calculations in perl, but I wonder if there is direct way in SQL?
Thanks for any help.
Ivan
-----
Iyengar Yoga Resources / GT Plugins
Now, I have a list 'users' and I want to retrieve the following information: an order list of 'products' which are the most popular among those 'users'. I only need the number of users for a given product, and I need the product ordered by the number of users.
So, if my user list is (1,2,3,4) and my table is something like
Code:
user product ------------
1 a
2 b
3 b
4 c
2 a
4 a
5 c
6 c
7 c
Code:
product number --------------
a 3
b 2
c 1
I know who I can do that by a simple select statement ordering on the 'products', and some calculations in perl, but I wonder if there is direct way in SQL?
Thanks for any help.
Ivan
-----
Iyengar Yoga Resources / GT Plugins
select, sort problem