list products of the top 5 users with the highest number of products (was "Query")

muscailie · #1 (**permalink**) 10-23-07, 09:24

Hello, i`m trying since morning to get a result.

I have a users table and a table which holds products for selling..
Each user has the option to mark several products as 'special'.

i need a list which contains the name of the 'special' products which belong to the first 5 users who have the highest number of products.

users(user_id,user_name)
products(product_name,product_special,user_id)

select count(user_id),user_name as no
from users
natural join products
order by no desc
limit 5 -> this query shows the first 5 users which have the most products

select product_name,user_name
natural join users
where product_special='1' -> this query shows all the 'special' products

I need the result of the two queries combined somehow.

thanks!

r937 · #2 (**permalink**) 10-23-07, 09:56

Code:

select products.product_name
     , products.user_id
  from users as T
inner
  join products
    on products.user_id = T.user_id
   and products.product_special = 1
 where (
       select count(*) 
         from (
              select user_id 
                   , count(*) as productcount
                from products
              group 
                  by user_id
              ) as c
        where productcount > (
              select count(*) 
                from products
               where user_id = T.user_id
              ) 
       ) < 5

Internet Services

Web Development

Digital Marketing

Consumer Tech