Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2005
    Posts
    2

    Unanswered: relation tables and counting

    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?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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 */
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2005
    Posts
    2
    whoa, I did not realize mySQL allowed things like that, thanks a lot for the help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •