relation tables and counting

sgusev · #1 (**permalink**) 12-28-05, 16:21

I have a table of users and a table of products, each with their own unique ids, I also have a relation table linking the user ids with each of the object ids they posess. I am trying to figure out a select call that would return a list of all products belonging to a user, with each row containing the product name (joined from the products table) and also the total number of users who have that product. Is there any way to do this with one SQL call?

r937 · #2 (**permalink**) 12-28-05, 22:36

Code:

select p.name
     , ( select count(*)
           from userproducts
          where product_id 
              = p.id )    as product_users
  from users as u
inner
  join userproducts as up
    on up.user_id = u.id
inner
  join products as p
    on p.id = up.product_id       
 where u.id = 937   /* which user */

sgusev · #3 (**permalink**) 12-29-05, 14:15

whoa, I did not realize mySQL allowed things like that, thanks a lot for the help.

Internet Services

Web Development

Digital Marketing

Consumer Tech