0

I have these tables:

# tb_shops

shop_id PK
shop_name

# tb_products

product_id PK
product_name
shop_id FK

I need to list all 'shops' with 4 products but some shops has more than 4 products.

I tried this query:

select a.shop_id,
        a.shop_name,
        count(b.product_id) as ptotal,
        group_concat(c.product_name separator ',') as products 
from tb_shops a 
left join tb_products b on a.shop_id=b.shop_id 
left join (
    select product_name,shop_id 
    from tb_products 
    limit 4) as c 
    on a.shop_id=c.shop_id 
group by a.shop_id 
order by a.shop_name asc

But, it doesn't work properly. It returns duplicate products. If I use distinct in group_concat it doesn't come with 4 products of the shop, really strange... someone knows a better solution?

Makyen
  • 31,849
  • 12
  • 86
  • 121
Awsj2hd8
  • 19
  • 7
  • The subquery returns any 4 rows from the table, not 4 rows for each shop. – Barmar Feb 04 '15 at 23:04
  • See http://stackoverflow.com/questions/2129693/mysql-using-limit-within-group-by-to-get-n-results-per-group for how to get 4 records for each group. – Barmar Feb 04 '15 at 23:05
  • I see some people saying about substring_index, but i think that is performance hit cuz maybe some shop can have 500 items or 1000 and substring_index will only limit the output but these 500 items will come in query – Awsj2hd8 Feb 05 '15 at 10:39
  • Unfortunately, there are no really great solutions for top-N per group in MySQL, so you have to choose the least of evils. – Barmar Feb 05 '15 at 15:26

1 Answers1

0

Well i need to list all 'shops' with 4 products but some shops has more than 4 products

You could use the having keyword and get rid of some joins

select a.shop_id,
       a.shop_name,
       count(b.product_id) as ptotal,
       group_concat(b.product_name separator ',') as products
from tb_shops a
join tb_products b on a.shop_id = b.shop_id
group by b.shop_id
having count(b.product_id) = 4
order by a.shop_name asc;

This will fix your issue. Basically what yo do here is do a normal join on botch table, then group by shop_id and finally only select those whose count of group is equal to 4. No need to use two left joins here.

Jean-François Savard
  • 20,626
  • 7
  • 49
  • 76