CODEWITHSUNDEEP
mysqlsqlselectjoin
StaticMike
StaticMike

Reputation: 1

MySQL: Limit number of results for each group in query based on results from another query

I would like to thank everyone in advance for any help you would share on this following issue.

In mysql I have the following tables:

select id, product_id from MembersProducts;

Member Products

id | product_id
1  | 2
1  | 3
1  | 4
1  | 5
1  | 6
2  | 5
2  | 6

select id, (TimesPerDay * TimesPerWeek) from Schedule;

Schedule
id | (TimesPerDay * TimesPerWeek)
1  | 2
2  | 1

I would like to create a query that generates the following table:

Member Products

id | product_id
1  | 2
1  | 3
2  | 5

My goal is to have the number of product_id limited by the (TimesPerDay * TimesPerWeek) column for each id.

Upvotes: 0

Views: 555

Answers (2)

mathematical.coffee
mathematical.coffee

Reputation: 56935

SELECT mp.*
FROM MembersProducts mp
LEFT JOIN Schedule s ON mp.id=s.id
WHERE (SELECT COUNT(*)
       FROM MembersProducts mp2
       WHERE mp2.id=mp.id
       AND mp2.product_id<mp.product_id
) < s.TimesPerDay*s.TimesPerWeek

What this does is basically a "greatest-n-per-group" selection where n varies per group too.

The WHERE counts up how many other products have already been chosen and only selects them out if it hasn't chosen enough (enough being TimesPerDay*TimesPerWeek).

Upvotes: 1

Francisco Soto
Francisco Soto

Reputation: 10392

select e.id, e.product_id 
from (select @rank:=CASE WHEN @ranked <> id THEN 1 ELSE @rank+1 END as rank,
          id,
          product_id, 
          @ranked:=id 
      from 
          (select @rank := -1) a, 
          (select @ranked :=- 1) b, 
          (select * from member_products order by id) c
      ) e 
inner join (select id, (TimesPerDay * TimesPerWeek) as times from schedule) sch 
    on sch.id = e.id and e.rank <= sch.times;

Ugly looking, but this will do exactly what you need.

Upvotes: 0

Related Questions